我试图了解整数分区表是如何工作的。然而,到目前为止,我还无法创建一个。
这个查询有什么问题:
#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
我得到这个错误:
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>))
问题是,尽管GENERATE_ARRAY
被记录为GENERATE_ARRAY(start_expression, end_expression [, step_expression])
,意味着step_expression
是可选的,但对于RANGE_BUCKET
,它是强制性的。
因此,以下操作将起作用:
#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