Inserting data into partitioned table using bigquery does not insert data

Question

I am trying to insert data using INSERT INTO DML command into partitioned BigQuery table from a non-partitioned table .

Steps followed :

1.Creating empty partitioned table

CREATE TABLE project.dataset.tbl1 ( field1 STRING, field2 STRING , field3 TIMESTAMP) PARTITION BY DATE(field3) OPTIONS( partition_expiration_days=3, description="a table partitioned " )

2.Inserting data from table2 to table1

INSERT INTO dataset.tbl1(field1, field2,field3) AS select f1, f2,f3 from project.dataset.tbl2 where DATE(f3) IN ('2018-09-13','2018-09-14','2018-09-15','2018-09-16') and f1 is not null and f2 is not null

The above DML statement gets executed but no records are inserted .So I check whether the SELECT query gets data or not.

The below fetches 13 records.

select f1, f2,f3 from project.dataset.tbl2 where DATE(f3) IN ('2018-09-13','2018-09-14','2018-09-15','2018-09-16') and f1 is not null and f2 is not null

data usually shows up with a bit of delay, have you tried running the query again without cache — Pentium10
– Pentium10, Commented Sep 26, 2018 at 12:37
The problem got resolved when removed "partition_expiration_days" — codninja0908
– codninja0908, Commented Sep 26, 2018 at 17:17
Since it got resolved after removing "partition_expiration_days" - how long was this set up for? — Felipe Hoffa
– Felipe Hoffa, Commented Sep 27, 2018 at 5:24

Hua Zhang · Accepted Answer · 2018-09-28 00:31:29Z

1

When you set partition_expiration_days=3, it means all partitions older than 3 days should expire and be deleted. You inserted data from 9/13 to 9/16, and since you posted yesterday I assume you ran the query on 9/26. So the data expired immediately after inserted to the table.

answered Sep 28, 2018 at 0:31

Hua Zhang

1,56111 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codninja0908 Over a year ago

right , so the observation was if the partition date lies within partition_expiration_days=3 , ie if current_date is 2019-09-26 and partition_expiration_days is 3 so date data that can be handled is 2018-09-24, 2018-09-25 and not 2018-09-23/2018-09-22 in partitions.

Collectives™ on Stack Overflow

Inserting data into partitioned table using bigquery does not insert data

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related