Skip to content
This repository was archived by the owner on Mar 23, 2026. It is now read-only.

Commit 5358620

Browse files
stephaniewang526stephaniewang526
authored
docs(samples): add export query results to Amazon S3 sample and IT (#853)
* docs(samples): add export query results to Amazon S3 sample and IT * update based on comments
1 parent 725c93f commit 5358620

File tree

4 files changed

+163
-4
lines changed

4 files changed

+163
-4
lines changed

‎samples/snippets/src/main/java/com/example/bigquery/ExportQueryResultsToS3.java‎

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
/*
2+
* Copyright 2020 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.example.bigquery;
18+
19+
// [START bigquery_omni_export_query_result_to_s3]
20+
import com.google.cloud.bigquery.BigQuery;
21+
import com.google.cloud.bigquery.BigQueryException;
22+
import com.google.cloud.bigquery.BigQueryOptions;
23+
import com.google.cloud.bigquery.QueryJobConfiguration;
24+
import com.google.cloud.bigquery.TableResult;
25+
26+
// Sample to export query results to Amazon S3 bucket
27+
public class ExportQueryResultsToS3 {
28+
29+
public static void main(String[] args) throws InterruptedException {
30+
// TODO(developer): Replace these variables before running the sample.
31+
String projectId = "MY_PROJECT_ID";
32+
String datasetName = "MY_DATASET_NAME";
33+
String externalTableName = "MY_EXTERNAL_TABLE_NAME";
34+
// connectionName should be in the format of connection_region.connection_name. e.g.
35+
// aws-us-east-1.s3-write-conn
36+
String connectionName = "MY_CONNECTION_REGION.MY_CONNECTION_NAME";
37+
// destinationUri must contain exactly one * anywhere in the leaf directory of the path string
38+
// e.g. ../aa/*, ../aa/b*c, ../aa/*bc, and ../aa/bc*
39+
// BigQuery replaces * with 0000..N depending on the number of files exported.
40+
// BigQuery determines the file count and sizes.
41+
String destinationUri = "s3://your-bucket-name/*";
42+
String format = "EXPORT_FORMAT";
43+
// Export result of query to find states starting with 'W'
44+
String query =
45+
String.format(
46+
"EXPORT DATA WITH CONNECTION %s AS SELECT * FROM %s.%s.%s WHERE name LIKE 'W%%'",
47+
connectionName, destinationUri, format, projectId, datasetName, externalTableName);
48+
exportQueryResultsToS3(query);
49+
}
50+
51+
public static void exportQueryResultsToS3(String query) throws InterruptedException {
52+
try {
53+
// Initialize client that will be used to send requests. This client only needs to be created
54+
// once, and can be reused for multiple requests.
55+
BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
56+
57+
TableResult results = bigquery.query(QueryJobConfiguration.of(query));
58+
59+
results
60+
.iterateAll()
61+
.forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));
62+
63+
System.out.println("Query results exported to Amazon S3 successfully.");
64+
} catch (BigQueryException e) {
65+
System.out.println("Query not performed \n" + e.toString());
66+
}
67+
}
68+
}
69+
// [END bigquery_omni_export_query_result_to_s3]

‎samples/snippets/src/main/java/com/example/bigquery/ResourceCleanUp.java‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -49,9 +49,9 @@ public static void main(String[] args) {
4949
for (Dataset dataset : datasets.getValues()) {
5050
String datasetName = dataset.getDatasetId().getDataset();
5151
if ((datasetName.contains("CREATE_DATASET_AWS_TEST_")
52-
|| datasetName.contains("MY_DATASET_")
53-
|| datasetName.contains("gcloud_test_")
54-
|| datasetName.contains("SHARED_DATASET_TEST_"))
52+
|| datasetName.contains("MY_DATASET_")
53+
|| datasetName.contains("gcloud_test_")
54+
|| datasetName.contains("SHARED_DATASET_TEST_"))
5555
// && dataset.getCreationTime() > sixHourAgo
5656
) {
5757
System.out.format("\tDeleting Dataset: %s\n", datasetName);

‎samples/snippets/src/test/java/com/example/bigquery/CreateExternalTableAwsIT.java‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,6 @@
3737
public class CreateExternalTableAwsIT {
3838

3939
private static final String ID = UUID.randomUUID().toString().substring(0, 8);
40-
private static final String LOCATION = "aws-us-east-1";
4140
private final Logger log = Logger.getLogger(this.getClass().getName());
4241
private String tableName;
4342
private ByteArrayOutputStream bout;

‎samples/snippets/src/test/java/com/example/bigquery/ExportQueryResultsToS3IT.java‎

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,91 @@
1+
/*
2+
* Copyright 2020 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.example.bigquery;
18+
19+
import static com.google.common.truth.Truth.assertThat;
20+
import static junit.framework.TestCase.assertNotNull;
21+
22+
import java.io.ByteArrayOutputStream;
23+
import java.io.PrintStream;
24+
import java.util.logging.Level;
25+
import java.util.logging.Logger;
26+
import org.junit.After;
27+
import org.junit.Before;
28+
import org.junit.BeforeClass;
29+
import org.junit.Test;
30+
31+
public class ExportQueryResultsToS3IT {
32+
private final Logger log = Logger.getLogger(this.getClass().getName());
33+
private ByteArrayOutputStream bout;
34+
private PrintStream out;
35+
private PrintStream originalPrintStream;
36+
37+
private static final String OMNI_PROJECT_ID = requireEnvVar("OMNI_PROJECT_ID");
38+
private static final String OMNI_DATASET_NAME = requireEnvVar("OMNI_DATASET_NAME");
39+
private static final String OMNI_EXTERNAL_TABLE_NAME = requireEnvVar("OMNI_EXTERNAL_TABLE_NAME");
40+
private static final String AWS_WRITE_CONNECTION_ID = requireEnvVar("AWS_WRITE_CONNECTION_ID");
41+
42+
private static String requireEnvVar(String varName) {
43+
String value = System.getenv(varName);
44+
assertNotNull(
45+
"Environment variable " + varName + " is required to perform these tests.",
46+
System.getenv(varName));
47+
return value;
48+
}
49+
50+
@BeforeClass
51+
public static void checkRequirements() {
52+
requireEnvVar("OMNI_PROJECT_ID");
53+
requireEnvVar("OMNI_DATASET_NAME");
54+
requireEnvVar("OMNI_EXTERNAL_TABLE_NAME");
55+
requireEnvVar("AWS_WRITE_CONNECTION_ID");
56+
}
57+
58+
@Before
59+
public void setUp() {
60+
bout = new ByteArrayOutputStream();
61+
out = new PrintStream(bout);
62+
originalPrintStream = System.out;
63+
System.setOut(out);
64+
}
65+
66+
@After
67+
public void tearDown() {
68+
// restores print statements in the original method
69+
System.out.flush();
70+
System.setOut(originalPrintStream);
71+
log.log(Level.INFO, bout.toString());
72+
}
73+
74+
@Test
75+
public void testQueryExternalTableAws() throws InterruptedException {
76+
String destinationUri = "s3://omni-samples-test-bucket/client-lib-test*";
77+
String format = "CSV";
78+
String query =
79+
String.format(
80+
"EXPORT DATA WITH CONNECTION `%s` OPTIONS(uri='%s', format='%s') "
81+
+ "AS SELECT * FROM %s.%s.%s WHERE name LIKE 'W%%'",
82+
AWS_WRITE_CONNECTION_ID,
83+
destinationUri,
84+
format,
85+
OMNI_PROJECT_ID,
86+
OMNI_DATASET_NAME,
87+
OMNI_EXTERNAL_TABLE_NAME);
88+
ExportQueryResultsToS3.exportQueryResultsToS3(query);
89+
assertThat(bout.toString()).contains("Query results exported to Amazon S3 successfully.");
90+
}
91+
}

0 commit comments

Comments
 (0)