CODEWITHSUNDEEP
google-bigquery
Ipkiss
Ipkiss

Reputation: 791

How to set to timestamp when creating a partitioned table in bigquery

This is what i have tried 

#standardSQL
 CREATE TABLE dataSet.myTableName (userName string, DateCreated DATE, email string)
 PARTITION BY DateCreated
 OPTIONS(
   description="a table partitioned by DateCreated"
 )

but in this case the DateCreated field is of Date type, I wanted to make it TIMESTAMP format, how do i do that? because my data is comes in this format "DateCreated":"1519302159.042" not in DATE type

Upvotes: 0

Views: 2159

Answers (2)

R Sun
R Sun

Reputation: 1671

You can try something like this:

CREATE TABLE
  blockchains.timestamp_based_partitioned_transactions ( 
    status STRING NOT NULL,
    transactionHash STRING NOT NULL,
    blockNumber INT64 NOT NULL,
    sender STRING NOT NULL,
    receiver STRING NOT NULL,
    timestamp TIMESTAMP NOT NULL
    )
PARTITION BY
   DATE(timestamp)

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33705

You won't be able to load data of that form directly into the BigQuery table. You have a couple of options, though:

  • Use a staging table.
    1. Create a separate table where the DateCreated column has type FLOAT64.
    2. Load your data into this staging table from CSV, JSON, or whatever format it's in.
    3. Run an INSERT statement to insert into the partitioned table dataSet.myTableName. You can convert DateCreated using DATE(TIMESTAMP_MILLIS(CAST(DateCreated * 1000 AS INT64))) AS DateCreated; this takes the FLOAT64 column and converts it to a DATE. If desired, you can pass a time zone to the DATE function. The default is UTC.
  • Use a tool outside of BigQuery to process the data before loading it.
    • Cloud Dataprep is designed for the purpose of processing data.
    • Cloud Dataflow lets you run custom code to process data and scale as needed.

My personal opinion: I would use BigQuery for all of your processing needs since you can just run queries.

Upvotes: 2

Related Questions