CODEWITHSUNDEEP
javagoogle-bigquery
Abhishanth Padarthy
Abhishanth Padarthy

Reputation: 31

How to create a partitioned table in BigQuery from java?

I would like to create a partitioned table(partition by field which is of DATE type) in BigQuery from java. I searched a lot but there is not much information on this. The code I used is

        TimePartitioning timePartitioning = TimePartitioning.of(TimePartitioning.Type.DAY);
        timePartitioning.toBuilder().setField("col3");
        TableDefinition tableDefinition = StandardTableDefinition.newBuilder().setSchema(schema2).setTimePartitioning(timePartitioning).build();
        TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
        bigquery.create(tableInfo);

Here, I have a couple of questions

  1. Should we use TimePartitioning even if we want to partition by date?
  2. I am not able to see the column name near 'Partitioned on field' in the BigQuery UI. I used this as reference. I had to use TimePartitioning class and not TimePartitioningBuilder because setTimePartitioning() accepts TimePartitioning only.

Upvotes: 3

Views: 474

Answers (2)

winwiz1
winwiz1

Reputation: 3174

I haven't tried but I'd use this table creation sample replacing the one-liner for StandardTableDefinition

TableDefinition tableDefinition = StandardTableDefinition.of(schema);

with the code taken from here. You could borrow the StandardTableDefinition creation/configuration options that make sense for you and then replace the one-liner for TimePartitioning 

TimePartitioning TIME_PARTITIONING = TimePartitioning.of(TimePartitioning.Type.DAY, 42);

with code taken from there e.g.

TimePartitioning TIME_PARTITIONING =
      TimePartitioning.newBuilder(TYPE)
          .setExpirationMs(EXPIRATION_MS)
          .setRequirePartitionFilter(REQUIRE_PARTITION_FILTER)
          .setField(FIELD)
          .build();

Use .setRequirePartitionFilter(...) only if you would like to disallow queries that doesn't take advantage of partitioning.

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59375

Easiest way would be to issue a standard query - if you can query from Java (which you already do?), just send a query like this:

#standardSQL
CREATE TABLE `project.dataset.table`
(
   x INT64 OPTIONS(description="An optional INTEGER field"),
   y STRUCT<
     a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
     b BOOL
   >, 
   date_column DATE
)
PARTITION BY date_column
CLUSTER BY i_recommend_you_to_choose_a_clustering_column

Upvotes: 2

Related Questions