Skip to content

Commit 8222086

Browse files
alixhamialixhami
committed
moves extra query samples into separate file
1 parent 562656d commit 8222086

File tree

4 files changed

+428
-341
lines changed

4 files changed

+428
-341
lines changed

‎google-cloud-examples/src/main/java/com/google/cloud/examples/bigquery/snippets/BigQuerySnippets.java‎

Lines changed: 16 additions & 273 deletions
Original file line numberDiff line numberDiff line change
@@ -71,10 +71,6 @@
7171
import java.util.Map;
7272
import java.util.Map.Entry;
7373
import java.util.concurrent.TimeoutException;
74-
import org.joda.time.DateTime;
75-
import org.joda.time.DateTimeZone;
76-
import org.joda.time.format.DateTimeFormatter;
77-
import org.joda.time.format.ISODateTimeFormat;
7874

7975
/**
8076
* This class contains a number of snippets for the {@link BigQuery} interface.
@@ -345,22 +341,24 @@ public long writeToTable(String datasetName, String tableName, String csvData)
345341
.setFormatOptions(FormatOptions.csv())
346342
.build();
347343
TableDataWriteChannel writer = bigquery.writer(writeChannelConfiguration);
348-
// Write data to writer
349-
try {
350-
writer.write(ByteBuffer.wrap(csvData.getBytes(Charsets.UTF_8)));
351-
} finally {
352-
writer.close();
353-
}
354-
// Get load job
355-
Job job = writer.getJob();
356-
job = job.waitFor();
357-
LoadStatistics stats = job.getStatistics();
358-
return stats.getOutputRows();
359-
// [END bigquery_java_untracked]
344+
// Write data to writer
345+
try {
346+
writer.write(ByteBuffer.wrap(csvData.getBytes(Charsets.UTF_8)));
347+
} finally {
348+
writer.close();
360349
}
350+
// Get load job
351+
Job job = writer.getJob();
352+
job = job.waitFor();
353+
LoadStatistics stats = job.getStatistics();
354+
return stats.getOutputRows();
355+
// [END bigquery_java_untracked]
356+
}
361357

362-
/** Example of creating a channel with which to write to a table. */
363-
// [TARGET writer(WriteChannelConfiguration)]
358+
/**
359+
* Example of creating a channel with which to write to a table.
360+
*/
361+
// [TARGET writer(JobId, WriteChannelConfiguration)]
364362
// [VARIABLE "my_dataset_name"]
365363
// [VARIABLE "my_table_name"]
366364
// [VARIABLE "StringValue1\nStringValue2\n"]
@@ -706,259 +704,4 @@ public void runQuery() throws InterruptedException {
706704
}
707705
// [END bigquery_query]
708706
}
709-
710-
/**
711-
* Example of running a Legacy SQL query.
712-
*/
713-
// [TARGET query(QueryJobConfiguration, JobOption...)]
714-
public void runLegacySqlQuery() throws InterruptedException {
715-
// [START bigquery_query_legacy]
716-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
717-
String query =
718-
"SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
719-
QueryJobConfiguration queryConfig =
720-
// To use legacy SQL syntax, set useLegacySql to true.
721-
QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();
722-
723-
// Print the results.
724-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
725-
for (FieldValue val : row) {
726-
System.out.printf("%s,", val.toString());
727-
}
728-
System.out.printf("\n");
729-
}
730-
// [END bigquery_query_legacy]
731-
}
732-
733-
/**
734-
* Example of running a query with Standard SQL explicitly set.
735-
*/
736-
// [TARGET query(QueryJobConfiguration, JobOption...)]
737-
public void runStandardSqlQuery() throws InterruptedException {
738-
// [START bigquery_query_standard]
739-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
740-
String query =
741-
"SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
742-
QueryJobConfiguration queryConfig =
743-
// Note that setUseLegacySql is set to false by default
744-
QueryJobConfiguration.newBuilder(query).setUseLegacySql(false).build();
745-
746-
// Print the results.
747-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
748-
for (FieldValue val : row) {
749-
System.out.printf("%s,", val.toString());
750-
}
751-
System.out.printf("\n");
752-
}
753-
// [END bigquery_query_standard]
754-
}
755-
756-
/**
757-
* Example of running a query and saving the results to a table.
758-
*/
759-
// [TARGET query(QueryJobConfiguration, JobOption...)]
760-
public void runQueryPermanentTable(String destinationDataset, String destinationTable) throws InterruptedException {
761-
// [START bigquery_query_destination_table]
762-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
763-
// String destinationDataset = 'my_destination_dataset';
764-
// String destinationTable = 'my_destination_table';
765-
String query =
766-
"SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
767-
QueryJobConfiguration queryConfig =
768-
// Note that setUseLegacySql is set to false by default
769-
QueryJobConfiguration.newBuilder(query)
770-
// Save the results of the query to a permanent table.
771-
.setDestinationTable(TableId.of(destinationDataset, destinationTable))
772-
.build();
773-
774-
// Print the results.
775-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
776-
for (FieldValue val : row) {
777-
System.out.printf("%s,", val.toString());
778-
}
779-
System.out.printf("\n");
780-
}
781-
// [END bigquery_query_destination_table]
782-
}
783-
784-
/**
785-
* Example of running a query and saving the results to a table.
786-
*/
787-
// [TARGET query(QueryJobConfiguration, JobOption...)]
788-
public void runQueryLargeResults(String destinationDataset, String destinationTable) throws InterruptedException {
789-
// [START bigquery_query_legacy_large_results]
790-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
791-
// String destinationDataset = 'my_destination_dataset';
792-
// String destinationTable = 'my_destination_table';
793-
String query =
794-
"SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
795-
QueryJobConfiguration queryConfig =
796-
// To use legacy SQL syntax, set useLegacySql to true.
797-
QueryJobConfiguration.newBuilder(query).setUseLegacySql(true)
798-
// Save the results of the query to a permanent table.
799-
.setDestinationTable(TableId.of(destinationDataset, destinationTable))
800-
// Allow results larger than the maximum response size.
801-
// If true, a destination table must be set.
802-
.setAllowLargeResults(true)
803-
.build();
804-
805-
// Print the results.
806-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
807-
for (FieldValue val : row) {
808-
System.out.printf("%s,", val.toString());
809-
}
810-
System.out.printf("\n");
811-
}
812-
// [END bigquery_query_legacy_large_results]
813-
}
814-
815-
/**
816-
* Example of running a query with the cache disabled.
817-
*/
818-
// [TARGET query(QueryJobConfiguration, JobOption...)]
819-
public void runUncachedQuery() throws TimeoutException, InterruptedException {
820-
// [START bigquery_query_no_cache]
821-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
822-
String query =
823-
"SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
824-
QueryJobConfiguration queryConfig =
825-
QueryJobConfiguration.newBuilder(query)
826-
// Disable the query cache to force live query evaluation.
827-
.setUseQueryCache(false)
828-
.build();
829-
830-
// Print the results.
831-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
832-
for (FieldValue val : row) {
833-
System.out.printf("%s,", val.toString());
834-
}
835-
System.out.printf("\n");
836-
}
837-
// [END bigquery_query_no_cache]
838-
}
839-
840-
/**
841-
* Example of running a batch query.
842-
*/
843-
// [TARGET query(QueryJobConfiguration, JobOption...)]
844-
public void runBatchQuery() throws TimeoutException, InterruptedException {
845-
// [START bigquery_query_batch]
846-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
847-
String query =
848-
"SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
849-
QueryJobConfiguration queryConfig =
850-
QueryJobConfiguration.newBuilder(query)
851-
// Run at batch priority, which won't count toward concurrent rate
852-
// limit. See:
853-
// https://cloud.google.com/bigquery/docs/running-queries#batch
854-
.setPriority(QueryJobConfiguration.Priority.BATCH)
855-
.build();
856-
857-
// Print the results.
858-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
859-
for (FieldValue val : row) {
860-
System.out.printf("%s,", val.toString());
861-
}
862-
System.out.printf("\n");
863-
}
864-
// [END bigquery_query_batch]
865-
}
866-
867-
/**
868-
* Example of running a query with named query parameters.
869-
*/
870-
// [TARGET query(QueryJobConfiguration, JobOption...)]
871-
public void runQueryWithNamedParameters() throws InterruptedException {
872-
// [START bigquery_query_params_named]
873-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
874-
String corpus = "romeoandjuliet";
875-
long minWordCount = 250;
876-
String query =
877-
"SELECT word, word_count\n"
878-
+ "FROM `bigquery-public-data.samples.shakespeare`\n"
879-
+ "WHERE corpus = @corpus\n"
880-
+ "AND word_count >= @min_word_count\n"
881-
+ "ORDER BY word_count DESC";
882-
// Note: Standard SQL is required to use query parameters.
883-
QueryJobConfiguration queryConfig =
884-
QueryJobConfiguration.newBuilder(query)
885-
.addNamedParameter("corpus", QueryParameterValue.string(corpus))
886-
.addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
887-
.build();
888-
889-
// Print the results.
890-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
891-
for (FieldValue val : row) {
892-
System.out.printf("%s,", val.toString());
893-
}
894-
System.out.printf("\n");
895-
}
896-
// [END bigquery_query_params_named]
897-
}
898-
899-
/**
900-
* Example of running a query with array query parameters.
901-
*/
902-
public void runQueryWithArrayParameters() throws InterruptedException {
903-
// [START bigquery_query_params_arrays]
904-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
905-
String gender = "M";
906-
String[] states = {"WA", "WI", "WV", "WY"};
907-
String query =
908-
"SELECT name, sum(number) as count\n"
909-
+ "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n"
910-
+ "WHERE gender = @gender\n"
911-
+ "AND state IN UNNEST(@states)\n"
912-
+ "GROUP BY name\n"
913-
+ "ORDER BY count DESC\n"
914-
+ "LIMIT 10;";
915-
// Note: Standard SQL is required to use query parameters.
916-
QueryJobConfiguration queryConfig =
917-
QueryJobConfiguration.newBuilder(query)
918-
.addNamedParameter("gender", QueryParameterValue.string(gender))
919-
.addNamedParameter("states", QueryParameterValue.array(states, String.class))
920-
.build();
921-
922-
// Print the results.
923-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
924-
for (FieldValue val : row) {
925-
System.out.printf("%s,", val.toString());
926-
}
927-
System.out.printf("\n");
928-
}
929-
// [END bigquery_query_params_arrays]
930-
}
931-
932-
/**
933-
* Example of running a query with timestamp query parameters.
934-
*/
935-
public void runQueryWithTimestampParameters() throws InterruptedException {
936-
// [START bigquery_query_params_timestamps]
937-
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
938-
DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC);
939-
String query = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
940-
// Note: Standard SQL is required to use query parameters.
941-
QueryJobConfiguration queryConfig =
942-
QueryJobConfiguration.newBuilder(query)
943-
.addNamedParameter(
944-
"ts_value",
945-
QueryParameterValue.timestamp(
946-
// Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
947-
timestamp.getMillis() * 1000))
948-
.build();
949-
950-
// Print the results.
951-
DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC();
952-
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
953-
System.out.printf(
954-
"%s\n",
955-
formatter.print(
956-
new DateTime(
957-
// Timestamp values are returned in microseconds since 1970-01-01T00:00:00 UTC,
958-
// but org.joda.time.DateTime constructor accepts times in milliseconds.
959-
row.get(0).getTimestampValue() / 1000, DateTimeZone.UTC)));
960-
System.out.printf("\n");
961-
}
962-
// [END bigquery_query_params_timestamps]
963-
}
964707
}

0 commit comments

Comments
 (0)