Inserting data into partitioned table using bigquery does not insert data

1
votes

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

1
google-bigquery
Can someone help on this asap – Neha0908
data usually shows up with a bit of delay, have you tried running the query again without cache – Pentium10
The problem got resolved when removed "partition_expiration_days" – Neha0908
Since it got resolved after removing "partition_expiration_days" - how long was this set up for? – Felipe Hoffa

1 Answers

1
votes

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.