CODEWITHSUNDEEP
google-bigquery
bry888
bry888

Reputation: 297

Create partitioned table with date suffix in bigquery using SQL or web UI

I want to create such table:

CREATE TABLE sometable
(SELECT columns, columns, date_col)
PARTITIONED BY date_col

And I want it to be date partitioned with the date in table suffix: sometable$date_partition

I read the docs, but can't complete this neither with web UI nor with SQL.

The web UI shows such error "Missing argument for parameter DATE."

My table name is "daily_export_${DATE}"
My partitioning column isn't blank, it's date_col.

Can I have a simple example, please?

Upvotes: 0

Views: 5887

Answers (2)

rmesteves
rmesteves

Reputation: 4095

As you can see in this documentation, BigQuery implements two different concepts: sharded tables and partitioned tables

The first one (sharded tables) is a way of dividing a whole table into many tables with a date suffix. You can query those tables individually or using wildcards. For example, instead of creating a single table named events, you can create many tables named events_20200101, events_20200102, [...]

When you do that, you are able to query any of those tables individually or you can query all of them by running some query like select * from events_*

The second concept (partitioned tables) is an approach to fragment your table in smaller pieces in order to improve the performance and reduce costs when querying data. Partitioned tables can be based on some column of your table or even on the ingestion time. When you table is partitioned by ingestion time you can access a pseudo column named _PARTITIONTIME

When comparing both approaches, the documentation says:

Date/timestamp partitioned tables perform better than tables sharded by date. When you create date-named tables, BigQuery must maintain a copy of the schema and metadata for each date-named table. Also, when date-named tables are used, BigQuery might be required to verify permissions for each queried table. This practice also adds to query overhead and impacts query performance. The recommended best practice is to use date/timestamp partitioned tables instead of date-sharded tables.

In your case, you basically need to create a partitioned table without a date in its name.

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59375

  • PARTITION BY goes earlier
  • The query needs to parse the table suffix into a DATE type.

For example:

CREATE OR REPLACE TABLE temp.so
PARTITION BY date_from_table_name
AS
SELECT PARSE_DATE('%Y%m%d', _table_suffix) date_from_table_name, event_timestamp, event_name, items  
FROM `bingo-blast-174dd.analytics_151321511.events_*`
WHERE _table_suffix BETWEEN '20200530' AND '20200531'
LIMIT 10

Upvotes: 2

Related Questions