Re: Bigquery table partition issue

GoodGuy · 02-13-2025 02:54 AM

hi, im newbie here, just try learning bigquery, and sorry for my bad english
i haved tried to different condition for partitioning my dummy date log,
but the table partition seems doesnt work. and number of partition is 0 when im checking on table info log2

my code query is

DROP TABLE IF EXISTS `belajar-my-project-84215.onbigquery.log2`;

CREATE OR REPLACE TABLE `belajar-my-project-84215.onbigquery.log2`

PARTITION BY DATE_TRUNC(date, month)

CLUSTER BY id AS

SELECT

id_karyawan AS id,

tanggal AS date,

time,

datetime

FROM

`belajar-my-project-84215.onbigquery.log1`

WHERE

tanggal IS NOT NULL; ---- this make sure the date

and this detail data for log1 and log2

Table log1

id is int64 have value range 1-5000,
date is date type value range is jan 1 2024 - dec 31 2024,
time is time type value range is time 24hour, each id have 2-3 value perdate
datetime is datetime type

Table ID

belajar-my-project-84215.onbigquery.log1

Created

Feb 12, 2025, 14:26:25 UTC+7

Last modified

Feb 12, 2025, 14:26:25 UTC+7

Table expiration

Apr 13, 2025, 14:26:25 UTC+7

Data location

asia-southeast2

Default collation

Default rounding mode

ROUNDING_MODE_UNSPECIFIED

Case insensitive

false

Description

Labels

Primary key(s)

Storage info

Number of rows

220,000

Number of partitions

0

mdideles

tried to replicate your script on my project. It seems to work fine for me. Can you try it 1 more time on your side? make sure to drop the table first before running the create table again.

GoodGuy

maybe more than 5 hours to trying another condition like using datetime colom, trunc by month, day, and ignore the null value. the result is still same.

but when im tried partition by date_trunc per year, the total data log1 and log2 is same, but the date is not partitioned becuase my data range is just 1 year..