CODEWITHSUNDEEP
sqlgoogle-bigquerybigdata
Soma Sekhar Kuruva
Soma Sekhar Kuruva

Reputation: 35

Create Partition table in Big Query

Can anyone please suggest how to create partition table in Big Query ?. Example: Suppose I have one log data in google storage for the year of 2016. I stored all data in one bucket partitioned by year , month and date wise. Here I want create table with partitioned by date. Thanks in Advance

Upvotes: 2

Views: 5255

Answers (3)

Sourygna
Sourygna

Reputation: 719

Having partitions for an External Table is not allowed as for now. There is a Feature Request for it:
https://issuetracker.google.com/issues/62993684
(please vote for it if you're interested in it!)

Google says that they are considering it.

Upvotes: 2

Pavan Edara
Pavan Edara

Reputation: 2305

Documentation for partitioned tables is here: https://cloud.google.com/bigquery/docs/creating-partitioned-tables

In this case, you'd create a partitioned table and populate the partitions with the data. You can run a query job that reads from GCS (and filters data for the specific date) and writes to the corresponding partition of a table. For example, to load data for May 1st, 2016 -- you'd specify the destination_table as table$20160501.

Currently, you'll have to run several query jobs to achieve this process. Please note that you'll be charged for each query job based on bytes processed.

Please see this post for some more details:

Migrating from non-partitioned to Partitioned tables

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

There are two options:

Option 1
You can load each daily file into separate respective table with name as YourLogs_YYYYMMDD
See details on how to Load Data from Cloud Storage

After tables created, you can access them either using Table wildcard functions (Legacy SQL) or using Wildcard Table (Standar SQL). See also Querying Multiple Tables Using a Wildcard Table for more examples

Option 2
You can create Date-Partitioned Table (just one table - YourLogs) - but you still will need to load each daily file into respective partition - see Creating and Updating Date-Partitioned Tables

After table is loaded you can easily Query Date-Partitioned Tables

Upvotes: 2

Related Questions