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

Commit fc3cf2a

Browse files
author
Praful Makani
authored
docs(samples): add omni create external table (#823)
1 parent 395de8d commit fc3cf2a

File tree

7 files changed

+224
-6
lines changed

7 files changed

+224
-6
lines changed

‎samples/install-without-bom/pom.xml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,12 @@
6666
<version>1.16.2</version>
6767
<scope>test</scope>
6868
</dependency>
69+
<dependency>
70+
<groupId>com.google.cloud</groupId>
71+
<artifactId>google-cloud-bigqueryconnection</artifactId>
72+
<version>0.4.0</version>
73+
<scope>test</scope>
74+
</dependency>
6975
<dependency>
7076
<groupId>junit</groupId>
7177
<artifactId>junit</artifactId>

‎samples/snapshot/pom.xml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,12 @@
6464
<version>1.16.2</version>
6565
<scope>test</scope>
6666
</dependency>
67+
<dependency>
68+
<groupId>com.google.cloud</groupId>
69+
<artifactId>google-cloud-bigqueryconnection</artifactId>
70+
<version>0.4.0</version>
71+
<scope>test</scope>
72+
</dependency>
6773
<dependency>
6874
<groupId>junit</groupId>
6975
<artifactId>junit</artifactId>

‎samples/snippets/pom.xml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -79,6 +79,12 @@
7979
<version>1.16.2</version>
8080
<scope>test</scope>
8181
</dependency>
82+
<dependency>
83+
<groupId>com.google.cloud</groupId>
84+
<artifactId>google-cloud-bigqueryconnection</artifactId>
85+
<version>0.4.0</version>
86+
<scope>test</scope>
87+
</dependency>
8288
<dependency>
8389
<groupId>junit</groupId>
8490
<artifactId>junit</artifactId>

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

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -28,19 +28,21 @@ public class CreateDatasetAws {
2828

2929
public static void main(String[] args) {
3030
// TODO(developer): Replace these variables before running the sample.
31+
String projectId = "MY_PROJECT_ID";
3132
String datasetName = "MY_DATASET_NAME";
3233
// Note: As of now location only supports aws-us-east-1
3334
String location = "aws-us-east-1";
34-
createDatasetAws(datasetName, location);
35+
createDatasetAws(projectId, datasetName, location);
3536
}
3637

37-
public static void createDatasetAws(String datasetName, String location) {
38+
public static void createDatasetAws(String projectId, String datasetName, String location) {
3839
try {
3940
// Initialize client that will be used to send requests. This client only needs to be created
4041
// once, and can be reused for multiple requests.
4142
BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
4243

43-
DatasetInfo datasetInfo = DatasetInfo.newBuilder(datasetName).setLocation(location).build();
44+
DatasetInfo datasetInfo =
45+
DatasetInfo.newBuilder(projectId, datasetName).setLocation(location).build();
4446

4547
Dataset dataset = bigquery.create(datasetInfo);
4648
System.out.println(

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

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
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 com.google.cloud.bigquery.BigQuery;
20+
import com.google.cloud.bigquery.BigQueryException;
21+
import com.google.cloud.bigquery.BigQueryOptions;
22+
import com.google.cloud.bigquery.CsvOptions;
23+
import com.google.cloud.bigquery.ExternalTableDefinition;
24+
import com.google.cloud.bigquery.TableId;
25+
import com.google.cloud.bigquery.TableInfo;
26+
27+
public class CreateExternalTableAws {
28+
29+
public static void main(String[] args) {
30+
// TODO(developer): Replace these variables before running the sample.
31+
String datasetName = "MY_DATASET_NAME";
32+
String tableName = "MY_TABLE_NAME";
33+
// Create a aws connection
34+
// projects/{project_id}/locations/{location_id}/connections/{connection_id}
35+
String connectionId = "MY_CONNECTION_NAME";
36+
String sourceUri = "s3://your-bucket-name/";
37+
CsvOptions options = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
38+
ExternalTableDefinition externalTableDefinition =
39+
ExternalTableDefinition.newBuilder(sourceUri, options)
40+
.setConnectionId(connectionId)
41+
.build();
42+
createExternalTableAws(datasetName, tableName, externalTableDefinition);
43+
}
44+
45+
public static void createExternalTableAws(
46+
String datasetName, String tableName, ExternalTableDefinition externalTableDefinition) {
47+
try {
48+
// Initialize client that will be used to send requests. This client only needs to be created
49+
// once, and can be reused for multiple requests.
50+
BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
51+
52+
TableId tableId = TableId.of(datasetName, tableName);
53+
TableInfo tableInfo = TableInfo.newBuilder(tableId, externalTableDefinition).build();
54+
55+
bigquery.create(tableInfo);
56+
System.out.println("Aws external table created successfully");
57+
} catch (BigQueryException e) {
58+
System.out.println("Aws external was not created." + e.toString());
59+
}
60+
}
61+
}

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -39,7 +39,7 @@ public class CreateDatasetAwsIT {
3939
private PrintStream out;
4040
private PrintStream originalPrintStream;
4141

42-
private static final String GOOGLE_CLOUD_PROJECT = requireEnvVar("OMNI_PROJECT_ID");
42+
private static final String PROJECT_ID = requireEnvVar("OMNI_PROJECT_ID");
4343

4444
private static String requireEnvVar(String varName) {
4545
String value = System.getenv(varName);
@@ -66,7 +66,7 @@ public void setUp() {
6666
@After
6767
public void tearDown() {
6868
// Clean up
69-
DeleteDataset.deleteDataset(GOOGLE_CLOUD_PROJECT, datasetName);
69+
DeleteDataset.deleteDataset(PROJECT_ID, datasetName);
7070
// restores print statements in the original method
7171
System.out.flush();
7272
System.setOut(originalPrintStream);
@@ -75,7 +75,7 @@ public void tearDown() {
7575

7676
@Test
7777
public void testCreateDatasetAws() {
78-
CreateDatasetAws.createDatasetAws(datasetName, LOCATION);
78+
CreateDatasetAws.createDatasetAws(PROJECT_ID, datasetName, LOCATION);
7979
assertThat(bout.toString()).contains("Aws dataset created successfully :");
8080
}
8181
}

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

Lines changed: 137 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,137 @@
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 com.google.cloud.bigquery.CsvOptions;
23+
import com.google.cloud.bigquery.ExternalTableDefinition;
24+
import com.google.cloud.bigquery.Field;
25+
import com.google.cloud.bigquery.Schema;
26+
import com.google.cloud.bigquery.StandardSQLTypeName;
27+
import com.google.cloud.bigquery.connection.v1.AwsCrossAccountRole;
28+
import com.google.cloud.bigquery.connection.v1.AwsProperties;
29+
import com.google.cloud.bigquery.connection.v1.Connection;
30+
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
31+
import com.google.cloud.bigquery.connection.v1.DeleteConnectionRequest;
32+
import com.google.cloud.bigquery.connection.v1.LocationName;
33+
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
34+
import java.io.ByteArrayOutputStream;
35+
import java.io.IOException;
36+
import java.io.PrintStream;
37+
import java.util.UUID;
38+
import java.util.logging.Level;
39+
import java.util.logging.Logger;
40+
import org.junit.After;
41+
import org.junit.Before;
42+
import org.junit.BeforeClass;
43+
import org.junit.Test;
44+
45+
public class CreateExternalTableAwsIT {
46+
47+
private static final String ID = UUID.randomUUID().toString().substring(0, 8);
48+
private static final String LOCATION = "aws-us-east-1";
49+
private final Logger log = Logger.getLogger(this.getClass().getName());
50+
private String datasetName;
51+
private String tableName;
52+
private String connectionName;
53+
private ByteArrayOutputStream bout;
54+
private PrintStream out;
55+
private PrintStream originalPrintStream;
56+
57+
private static final String PROJECT_ID = requireEnvVar("OMNI_PROJECT_ID");
58+
private static final String AWS_ACCOUNT_ID = requireEnvVar("AWS_ACCOUNT_ID");
59+
private static final String AWS_ROLE_ID = requireEnvVar("AWS_ROLE_ID");
60+
61+
private static String requireEnvVar(String varName) {
62+
String value = System.getenv(varName);
63+
assertNotNull(
64+
"Environment variable " + varName + " is required to perform these tests.",
65+
System.getenv(varName));
66+
return value;
67+
}
68+
69+
@BeforeClass
70+
public static void checkRequirements() {
71+
requireEnvVar("OMNI_PROJECT_ID");
72+
requireEnvVar("AWS_ACCOUNT_ID");
73+
requireEnvVar("AWS_ROLE_ID");
74+
}
75+
76+
@Before
77+
public void setUp() throws IOException {
78+
datasetName = "CREATE_EXTERNAL_TABLE_AWS_TEST_" + ID;
79+
tableName = "CREATE_EXTERNAL_TABLE_AWS_TEST_" + ID;
80+
connectionName = "CREATE_EXTERNAL_TABLE_AWS_TEST_" + ID;
81+
bout = new ByteArrayOutputStream();
82+
out = new PrintStream(bout);
83+
originalPrintStream = System.out;
84+
System.setOut(out);
85+
// create a temporary aws connection
86+
try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
87+
LocationName parent = LocationName.of(PROJECT_ID, LOCATION);
88+
String iamRoleId = String.format("arn:aws:iam::%s:role/%s", AWS_ACCOUNT_ID, AWS_ROLE_ID);
89+
AwsCrossAccountRole role = AwsCrossAccountRole.newBuilder().setIamRoleId(iamRoleId).build();
90+
AwsProperties awsProperties = AwsProperties.newBuilder().setCrossAccountRole(role).build();
91+
Connection connection = Connection.newBuilder().setAws(awsProperties).build();
92+
CreateConnectionRequest request =
93+
CreateConnectionRequest.newBuilder()
94+
.setParent(parent.toString())
95+
.setConnection(connection)
96+
.setConnectionId(connectionName)
97+
.build();
98+
connectionName = client.createConnection(request).getName();
99+
}
100+
// create a temporary dataset
101+
CreateDatasetAws.createDatasetAws(PROJECT_ID, datasetName, LOCATION);
102+
}
103+
104+
@After
105+
public void tearDown() throws IOException {
106+
// delete a temporary aws connection
107+
try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
108+
DeleteConnectionRequest request =
109+
DeleteConnectionRequest.newBuilder().setName(connectionName).build();
110+
client.deleteConnection(request);
111+
}
112+
// Clean up
113+
DeleteTable.deleteTable(datasetName, tableName);
114+
DeleteDataset.deleteDataset(PROJECT_ID, datasetName);
115+
// restores print statements in the original method
116+
System.out.flush();
117+
System.setOut(originalPrintStream);
118+
log.log(Level.INFO, bout.toString());
119+
}
120+
121+
@Test
122+
public void testCreateExternalTableAws() {
123+
String sourceUri = "s3://cloud-samples-tests/us-states.csv";
124+
Schema schema =
125+
Schema.of(
126+
Field.of("name", StandardSQLTypeName.STRING),
127+
Field.of("post_abbr", StandardSQLTypeName.STRING));
128+
CsvOptions options = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
129+
ExternalTableDefinition externalTable =
130+
ExternalTableDefinition.newBuilder(sourceUri, options)
131+
.setConnectionId(connectionName)
132+
.setSchema(schema)
133+
.build();
134+
CreateExternalTableAws.createExternalTableAws(datasetName, tableName, externalTable);
135+
assertThat(bout.toString()).contains("Aws external table created successfully");
136+
}
137+
}

0 commit comments

Comments
 (0)