private void generateTableWithDdl(String datasetId, String tableId) throws InterruptedException { String sql = String.format( "CREATE TABLE %s.%s " + "AS " + "SELECT " + "2000 + CAST(18 * RAND() as INT64) AS year, " + "IF(RAND() > 0.5,\"foo\",\"bar\") AS token " + "FROM " + "UNNEST(GENERATE_ARRAY(0,5,1)) AS r", datasetId, tableId); Job job = bigquery.create(JobInfo.of(QueryJobConfiguration.newBuilder(sql).build())); job.waitFor(); }
/** Example usage of {@code waitFor()}. */ // [TARGET waitFor(RetryOption...)] public boolean waitFor() throws InterruptedException { try { // [START ] Job completedJob = job.waitFor(); if (completedJob == null) { // job no longer exists } else if (completedJob.getStatus().getError() != null) { // job failed, handle error } else { // job completed successfully } // [END ] } catch (BigQueryException e) { // Timeouts shouldn't happen without a timeout option. if (e.getCause() instanceof PollException) { return false; } throw e; } return true; }
/** Example loading data from a list of Google Cloud Storage files. */ // [TARGET load(FormatOptions, List, JobOption...)] // [VARIABLE "gs://my_bucket/filename1.csv"] // [VARIABLE "gs://my_bucket/filename2.csv"] public Job loadList(String gcsUrl1, String gcsUrl2) { // [START ] List<String> sourceUris = new ArrayList<>(); sourceUris.add(gcsUrl1); sourceUris.add(gcsUrl2); Job job = table.load(FormatOptions.csv(), sourceUris); // Wait for the job to complete try { Job completedJob = job.waitFor( RetryOption.initialRetryDelay(Duration.ofSeconds(1)), RetryOption.totalTimeout(Duration.ofMinutes(3))); if (completedJob != null && completedJob.getStatus().getError() == null) { // Job completed successfully } else { // Handle error case } } catch (InterruptedException e) { // Handle interrupted wait } // [END ] return job; }
@Test public void testWaitFor_Null() throws InterruptedException { initializeExpectedJob(1); BigQuery.JobOption[] expectedOptions = {BigQuery.JobOption.fields(BigQuery.JobField.STATUS)}; expect(bigquery.getOptions()).andReturn(mockOptions); expect(mockOptions.getClock()).andReturn(CurrentMillisClock.getDefaultClock()); expect(bigquery.getJob(JOB_INFO.getJobId(), expectedOptions)).andReturn(null); replay(bigquery, mockOptions); initializeJob(); assertNull(job.waitFor(TEST_RETRY_OPTIONS)); verify(mockOptions); }
public static void main(String... args) throws InterruptedException, TimeoutException { BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); TableId tableId = TableId.of("dataset", "table"); Table table = bigquery.getTable(tableId); if (table == null) { System.out.println("Creating table " + tableId); Field integerField = Field.of("fieldName", LegacySQLTypeName.INTEGER); Schema schema = Schema.of(integerField); table = bigquery.create(TableInfo.of(tableId, StandardTableDefinition.of(schema))); } System.out.println("Loading data into table " + tableId); Job loadJob = table.load(FormatOptions.csv(), "gs://bucket/path"); loadJob = loadJob.waitFor(); if (loadJob.getStatus().getError() != null) { System.out.println("Job completed with errors"); } else { System.out.println("Job succeeded"); } } }
/** Example of copying multiple tables to a destination. */ public void copyTables(String datasetId, String destinationTableId) throws InterruptedException { generateTableWithDdl(datasetId, "table1"); generateTableWithDdl(datasetId, "table2"); // [START bigquery_copy_table_multiple_source] TableId destinationTable = TableId.of(datasetId, destinationTableId); CopyJobConfiguration configuration = CopyJobConfiguration.newBuilder( destinationTable, Arrays.asList(TableId.of(datasetId, "table1"), TableId.of(datasetId, "table2"))) .build(); // Copy the tables. Job job = bigquery.create(JobInfo.of(configuration)); job = job.waitFor(); // Check the table StandardTableDefinition table = bigquery.getTable(destinationTable).getDefinition(); System.out.println("State: " + job.getStatus().getState()); System.out.printf("Copied %d rows.\n", table.getNumRows()); // [END bigquery_copy_table_multiple_source] }
/** Example of loading a parquet file from GCS to a table. */ public void loadTableGcsParquet(String datasetName) throws InterruptedException { // [START bigquery_load_table_gcs_parquet] String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.parquet"; TableId tableId = TableId.of(datasetName, "us_states"); LoadJobConfiguration configuration = LoadJobConfiguration.builder(tableId, sourceUri) .setFormatOptions(FormatOptions.parquet()) .build(); // Load the table Job loadJob = bigquery.create(JobInfo.of(configuration)); loadJob = loadJob.waitFor(); // Check the table StandardTableDefinition destinationTable = bigquery.getTable(tableId).getDefinition(); System.out.println("State: " + loadJob.getStatus().getState()); System.out.printf("Loaded %d rows.\n", destinationTable.getNumRows()); // [END bigquery_load_table_gcs_parquet] }
/** Example of creating a channel with which to write to a table. */ // [TARGET writer(WriteChannelConfiguration)] // [VARIABLE "my_dataset_name"] // [VARIABLE "my_table_name"] // [VARIABLE "StringValue1\nStringValue2\n"] public long writeToTable(String datasetName, String tableName, String csvData) throws IOException, InterruptedException, TimeoutException { // [START ] TableId tableId = TableId.of(datasetName, tableName); WriteChannelConfiguration writeChannelConfiguration = WriteChannelConfiguration.newBuilder(tableId).setFormatOptions(FormatOptions.csv()).build(); TableDataWriteChannel writer = bigquery.writer(writeChannelConfiguration); // Write data to writer try { writer.write(ByteBuffer.wrap(csvData.getBytes(Charsets.UTF_8))); } finally { writer.close(); } // Get load job Job job = writer.getJob(); job = job.waitFor(); LoadStatistics stats = job.getStatistics(); return stats.getOutputRows(); // [END ] }
@Test public void testWaitFor() throws InterruptedException { initializeExpectedJob(2); BigQuery.JobOption[] expectedOptions = {BigQuery.JobOption.fields(BigQuery.JobField.STATUS)}; JobStatus status = createStrictMock(JobStatus.class); expect(status.getState()).andReturn(JobStatus.State.DONE); // caused by optimizing extra call to ResultRetryAlgorithm#shouldRetry() expect(status.getState()).andReturn(JobStatus.State.DONE); expect(bigquery.getOptions()).andReturn(mockOptions); expect(mockOptions.getClock()).andReturn(CurrentMillisClock.getDefaultClock()); Job completedJob = expectedJob.toBuilder().setStatus(status).build(); expect(bigquery.getJob(JOB_INFO.getJobId(), expectedOptions)).andReturn(completedJob); expect(bigquery.getJob(JOB_INFO.getJobId())).andReturn(completedJob); replay(status, bigquery, mockOptions); initializeJob(); assertSame(completedJob, job.waitFor(TEST_RETRY_OPTIONS)); verify(status, mockOptions); }
@Test public void testWaitForWithCheckingPeriod_Null() throws InterruptedException { initializeExpectedJob(2); BigQuery.JobOption[] expectedOptions = {BigQuery.JobOption.fields(BigQuery.JobField.STATUS)}; expect(bigquery.getOptions()).andReturn(mockOptions); expect(mockOptions.getClock()).andReturn(CurrentMillisClock.getDefaultClock()); Job runningJob = expectedJob.toBuilder().setStatus(new JobStatus(JobStatus.State.RUNNING)).build(); expect(bigquery.getJob(JOB_INFO.getJobId(), expectedOptions)).andReturn(runningJob); expect(bigquery.getJob(JOB_INFO.getJobId(), expectedOptions)).andReturn(null); replay(bigquery, mockOptions); initializeJob(); assertNull(job.waitFor(TEST_RETRY_OPTIONS)); verify(bigquery, mockOptions); }
/** Example of copying the table to a destination table. */ // [TARGET copy(String, String, JobOption...)] // [VARIABLE "my_dataset"] // [VARIABLE "my_destination_table"] public Job copy(String datasetName, String tableName) { // [START ] Job job = table.copy(datasetName, tableName); // Wait for the job to complete. try { Job completedJob = job.waitFor( RetryOption.initialRetryDelay(Duration.ofSeconds(1)), RetryOption.totalTimeout(Duration.ofMinutes(3))); if (completedJob != null && completedJob.getStatus().getError() == null) { // Job completed successfully } else { // Handle error case } } catch (InterruptedException e) { // Handle interrupted wait } // [END ] return job; }
/** Example extracting data to single Google Cloud Storage file. */ // [TARGET extract(String, String, JobOption...)] // [VARIABLE "CSV"] // [VARIABLE "gs://my_bucket/filename.csv"] public Job extractSingle(String format, String gcsUrl) { // [START bigquery_extract_table] Job job = table.extract(format, gcsUrl); // Wait for the job to complete try { Job completedJob = job.waitFor( RetryOption.initialRetryDelay(Duration.ofSeconds(1)), RetryOption.totalTimeout(Duration.ofMinutes(3))); if (completedJob != null && completedJob.getStatus().getError() == null) { // Job completed successfully } else { // Handle error case } } catch (InterruptedException e) { // Handle interrupted wait } // [END bigquery_extract_table] return job; }
/** Example usage of {@code waitFor()} with checking period and timeout. */ // [TARGET waitFor(RetryOption...)] public boolean waitForWithOptions() throws InterruptedException { try { // [START ] Job completedJob = job.waitFor( RetryOption.initialRetryDelay(Duration.ofSeconds(1)), RetryOption.totalTimeout(Duration.ofMinutes(1))); if (completedJob == null) { // job no longer exists } else if (completedJob.getStatus().getError() != null) { // job failed, handle error } else { // job completed successfully } // [END ] } catch (BigQueryException e) { if (e.getCause() instanceof PollException) { return false; } throw e; } return true; }
@Test public void testWaitForWithCheckingPeriod() throws InterruptedException { initializeExpectedJob(3); BigQuery.JobOption[] expectedOptions = {BigQuery.JobOption.fields(BigQuery.JobField.STATUS)}; JobStatus status = createStrictMock(JobStatus.class); expect(status.getState()).andReturn(JobStatus.State.RUNNING); // caused by optimizing extra call to ResultRetryAlgorithm#shouldRetry() expect(status.getState()).andReturn(JobStatus.State.RUNNING); expect(status.getState()).andReturn(JobStatus.State.DONE); // caused by optimizing extra call to ResultRetryAlgorithm#shouldRetry() expect(status.getState()).andReturn(JobStatus.State.DONE); expect(bigquery.getOptions()).andReturn(mockOptions); expect(mockOptions.getClock()).andReturn(CurrentMillisClock.getDefaultClock()); Job runningJob = expectedJob.toBuilder().setStatus(status).build(); Job completedJob = expectedJob.toBuilder().setStatus(status).build(); expect(bigquery.getJob(JOB_INFO.getJobId(), expectedOptions)).andReturn(runningJob); expect(bigquery.getJob(JOB_INFO.getJobId(), expectedOptions)).andReturn(completedJob); expect(bigquery.getJob(JOB_INFO.getJobId())).andReturn(completedJob); replay(status, bigquery, mockOptions); initializeJob(); assertSame(completedJob, job.waitFor(TEST_RETRY_OPTIONS)); verify(status, mockOptions); }
/** Example loading data from a single Google Cloud Storage file. */ // [TARGET load(FormatOptions, String, JobOption...)] // [VARIABLE "gs://my_bucket/filename.csv"] public Job loadSingle(String sourceUri) { // [START bigquery_load_table_gcs_csv] Job job = table.load(FormatOptions.csv(), sourceUri); // Wait for the job to complete try { Job completedJob = job.waitFor( RetryOption.initialRetryDelay(Duration.ofSeconds(1)), RetryOption.totalTimeout(Duration.ofMinutes(3))); if (completedJob != null && completedJob.getStatus().getError() == null) { // Job completed successfully } else { // Handle error case } } catch (InterruptedException e) { // Handle interrupted wait } // [END bigquery_load_table_gcs_csv] return job; } }
/** Example copying the table to a destination table. */ // [TARGET copy(TableId, JobOption...)] // [VARIABLE "my_dataset"] // [VARIABLE "my_destination_table"] public Job copyTableId(String dataset, String tableName) throws BigQueryException { // [START bigquery_copy_table] TableId destinationId = TableId.of(dataset, tableName); JobOption options = JobOption.fields(JobField.STATUS, JobField.USER_EMAIL); Job job = table.copy(destinationId, options); // Wait for the job to complete. try { Job completedJob = job.waitFor( RetryOption.initialRetryDelay(Duration.ofSeconds(1)), RetryOption.totalTimeout(Duration.ofMinutes(3))); if (completedJob != null && completedJob.getStatus().getError() == null) { // Job completed successfully. } else { // Handle error case. } } catch (InterruptedException e) { // Handle interrupted wait } // [END bigquery_copy_table] return job; }
/** Example of writing a local file to a table. */ // [TARGET writer(WriteChannelConfiguration)] // [VARIABLE "my_dataset_name"] // [VARIABLE "my_table_name"] // [VARIABLE FileSystems.getDefault().getPath(".", "my-data.csv")] // [VARIABLE "us"] public long writeFileToTable(String datasetName, String tableName, Path csvPath, String location) throws IOException, InterruptedException, TimeoutException { // [START bigquery_load_from_file] TableId tableId = TableId.of(datasetName, tableName); WriteChannelConfiguration writeChannelConfiguration = WriteChannelConfiguration.newBuilder(tableId).setFormatOptions(FormatOptions.csv()).build(); // The location must be specified; other fields can be auto-detected. JobId jobId = JobId.newBuilder().setLocation(location).build(); TableDataWriteChannel writer = bigquery.writer(jobId, writeChannelConfiguration); // Write data to writer try (OutputStream stream = Channels.newOutputStream(writer)) { Files.copy(csvPath, stream); } // Get load job Job job = writer.getJob(); job = job.waitFor(); LoadStatistics stats = job.getStatistics(); return stats.getOutputRows(); // [END bigquery_load_from_file] }
@Test public void testExtractJob() throws InterruptedException, TimeoutException { String tableName = "test_export_job_table"; TableId destinationTable = TableId.of(DATASET, tableName); LoadJobConfiguration configuration = LoadJobConfiguration.newBuilder(destinationTable, "gs://" + BUCKET + "/" + LOAD_FILE) .setSchema(SIMPLE_SCHEMA) .build(); Job remoteLoadJob = bigquery.create(JobInfo.of(configuration)); remoteLoadJob = remoteLoadJob.waitFor(); assertNull(remoteLoadJob.getStatus().getError()); ExtractJobConfiguration extractConfiguration = ExtractJobConfiguration.newBuilder(destinationTable, "gs://" + BUCKET + "/" + EXTRACT_FILE) .setPrintHeader(false) .build(); Job remoteExtractJob = bigquery.create(JobInfo.of(extractConfiguration)); remoteExtractJob = remoteExtractJob.waitFor(); assertNull(remoteExtractJob.getStatus().getError()); String extractedCsv = new String(storage.readAllBytes(BUCKET, EXTRACT_FILE), StandardCharsets.UTF_8); assertEquals( Sets.newHashSet(CSV_CONTENT.split("\n")), Sets.newHashSet(extractedCsv.split("\n"))); assertTrue(bigquery.delete(DATASET, tableName)); }
@Test public void testCancelJob() throws InterruptedException, TimeoutException { String destinationTableName = "test_cancel_query_job_table"; String query = "SELECT TimestampField, StringField, BooleanField FROM " + TABLE_ID.getTable(); TableId destinationTable = TableId.of(DATASET, destinationTableName); QueryJobConfiguration configuration = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setDestinationTable(destinationTable) .build(); Job remoteJob = bigquery.create(JobInfo.of(configuration)); assertTrue(remoteJob.cancel()); remoteJob = remoteJob.waitFor(); assertNull(remoteJob.getStatus().getError()); }
@Test public void testCopyJob() throws InterruptedException, TimeoutException { String sourceTableName = "test_copy_job_source_table"; String destinationTableName = "test_copy_job_destination_table"; TableId sourceTable = TableId.of(DATASET, sourceTableName); StandardTableDefinition tableDefinition = StandardTableDefinition.of(TABLE_SCHEMA); TableInfo tableInfo = TableInfo.of(sourceTable, tableDefinition); Table createdTable = bigquery.create(tableInfo); assertNotNull(createdTable); assertEquals(DATASET, createdTable.getTableId().getDataset()); assertEquals(sourceTableName, createdTable.getTableId().getTable()); TableId destinationTable = TableId.of(DATASET, destinationTableName); CopyJobConfiguration configuration = CopyJobConfiguration.of(destinationTable, sourceTable); Job remoteJob = bigquery.create(JobInfo.of(configuration)); remoteJob = remoteJob.waitFor(); assertNull(remoteJob.getStatus().getError()); Table remoteTable = bigquery.getTable(DATASET, destinationTableName); assertNotNull(remoteTable); assertEquals(destinationTable.getDataset(), remoteTable.getTableId().getDataset()); assertEquals(destinationTableName, remoteTable.getTableId().getTable()); assertEquals(TABLE_SCHEMA, remoteTable.getDefinition().getSchema()); assertTrue(createdTable.delete()); assertTrue(remoteTable.delete()); }