BigQuery: How to create integer partitioned table via DML?

Question

I try to understand how the integer partitioned tables work. So far however, I could not create one.

What is wrong with this query:

 #standardSQL
 CREATE or Replace TABLE temp.test_int_partition

 PARTITION BY RANGE_BUCKET(id, GENERATE_ARRAY(0,100))
 OPTIONS(
   description="test int partition"
 ) 
as 

WITH data as (
SELECT 12 as id, 'Alex' as name
UNION ALL 
SELECT 23 as id, 'Chimp' as name
)

SELECT *
from data

I'm getting this error:

Error: PARTITION BY expression must be DATE(<timestamp_column>), a DATE column, or RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<int64_value>, <int64_value>, <int64_value>))

score 0 · Accepted Answer · answered Feb 17 '20 at 09:38

0

The issue is that despite GENERATE_ARRAY being documented as GENERATE_ARRAY(start_expression, end_expression [, step_expression]), meaning step_expression being optional, for the RANGE_BUCKET it's mandatory.

So the following will work:

 #standardSQL
 CREATE or Replace TABLE temp.test_int_partition

 PARTITION BY RANGE_BUCKET(id, GENERATE_ARRAY(0,100,1))
 OPTIONS(
   description="test int partition"
 ) 
as 

WITH data as (
SELECT 12 as id, 'Alex' as name
UNION ALL 
SELECT 23 as id, 'Chimp' as name
)

SELECT *
from data

answered Feb 17 '20 at 09:38

Ilja

993
2
17
36

yes. After I tried entering the optional parameter it worked – Ilja Feb 17 '20 at 13:57
I can't for the next two days – Ilja Feb 17 '20 at 15:41

BigQuery: How to create integer partitioned table via DML?

1 Answers1