CODEWITHSUNDEEP
google-bigquery
Der Gnirreh
Der Gnirreh

Reputation: 306

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

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]

Upvotes: 0

Views: 680

Answers (2)

Der Gnirreh
Der Gnirreh

Reputation: 306

This issue has now been solved by Google, so the following command works as expected:

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

Upvotes: 0

rsantiago
rsantiago

Reputation: 2099

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.

Upvotes: 2

Related Questions