Why don't decorators work on integer range partitioned bigquery tables?

Question

I created an integer range partitioned bigquery table similar to one described in the tutorial:

 CREATE TABLE
   mydataset.newtable
 PARTITION BY
   RANGE_BUCKET(customer_id, GENERATE_ARRAY(1, 100, 1))
 AS SELECT 1 AS customer_id, DATE "2019-10-01" AS date1

However, trying to extract a single partition into a bucket, running in bash

bq extract myproject:mydataset.newtable\$1 gs://mybucket/newtable.csv

I get an error "partition key is not valid". Why? How do I find the valid keys?

Similarly I cannot use the decorator to select from a specific partition using query composer: select from mydataset.newtable$0 or select from mydataset.newtable$1 give

Syntax error: Illegal input character "$" at [1:46]

Interesting, their documentation says this should work. cloud.google.com/bigquery/docs/… . Might need to submit a bug report. — rtenha
Looks like it's not supported by extract. I tried "bq head" with partition decorator and it works fine. — Hua Zhang

rsantiago rsantiago · Accepted Answer · 2020-02-06T23:27:52

The decorator $ is valid in LegacySQL, but you can opt by one of these options:

# LegacySQL, legacy sql is used by default in the following command. 
# From the UI you need to change it in More -> Query Settings
bq query 'SELECT * from mydataset.newtable$10'

or

# StandardSQL, the option use_legacy_sql=false force to use standard sql 
bq query --use_legacy_sql=false 'SELECT * from mydataset.newtable WHERE customer_id BETWEEN 10 AND 20'

Regarding the bq extract command I could export after removing :

$ bq extract myproject:mydataset.newtable$1 gs://mybucket/newtable.csv
Waiting on bqjob_..._000001701cb5d260_1 ... (0s) Current status: DONE   
$ gsutil cat gs://mybucket/newtable.csv
customer_id,date1
18,2020-10-01
1,2019-10-01
2,2019-10-02
$

Edit:

After checking your comment below, you are correct, the bq extract above returns all the data.

The doc Exporting table data suggests that 'mydataset.table$N'should work. But when the scape character (\) is used, this error is returned: Partition key is invalid: key: "N"

Since there are not documentation that indicates this is possible, I have already created a FR to add this funtionality. You can monitor this request in this link, it is important to note that there is not an ETA for its resolution.

Why don't decorators work on integer range partitioned bigquery tables?

2 Answers