CODEWITHSUNDEEP
google-bigquery
user1367204
user1367204

Reputation: 4797

Is there a way to set the maximum length of the string in the column's name in BigQuery?

I can't find any documentation for it, should I believe that it is a maximum of 128 characters?

Upvotes: 0

Views: 4364

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33755

Let's check by generating a long column name:

#standardSQL
SELECT
  STRING_AGG(
    CODE_POINTS_TO_STRING([MOD(c, 26) + TO_CODE_POINTS('a')[OFFSET(0)]]),
    '')
FROM UNNEST(GENERATE_ARRAY(0, 127)) AS c;
+----------------------------------------------------------------------------------------------------------------------------------+
|                                                               f0_                                                                |
+----------------------------------------------------------------------------------------------------------------------------------+
| abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx |
+----------------------------------------------------------------------------------------------------------------------------------+

Now we can use it in a query:

bq query --use_legacy_sql=false "SELECT 1 AS abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx;"
Waiting on <job ID> ... (1s) Current status: DONE   
+----------------------------------------------------------------------------------------------------------------------------------+
| abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx |
+----------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                1 |
+----------------------------------------------------------------------------------------------------------------------------------+

Okay, so 128 characters is all right. What if we use one more?

bq query --use_legacy_sql=false "SELECT 1 AS abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy;"
Waiting on <job ID> ... (0s) Current status: DONE   
BigQuery error in query operation: Error processing job 'bigquerytestdefault:bqjob_r5056943d6408b629_0000015cc29ae7ae_1': Invalid field name "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy". Fields
must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.

I get an error about the length of the name. This is documented as part of the tables reference, saying:

[Required] The field name. The name must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_), and must start with a letter or underscore. The maximum length is 128 characters.

Upvotes: 3

Related Questions