Examples with CsvOptions com.google.cloud.bigquery.CsvOptions used on opensource projects

Example 1 with CsvOptions

use of com.google.cloud.bigquery.CsvOptions in project java-bigquery by googleapis.

the class RelaxColumnLoadAppend method relaxColumnLoadAppend.

public static void relaxColumnLoadAppend(String datasetName, String tableName, String sourceUri) {
    try {
        // Initialize client that will be used to send requests. This client only needs to be created
        // once, and can be reused for multiple requests.
        BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
        // Retrieve destination table reference
        Table table = bigquery.getTable(TableId.of(datasetName, tableName));
        // column as a 'REQUIRED' field.
        Field name = Field.newBuilder("name", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();
        Field postAbbr = Field.newBuilder("post_abbr", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();
        Schema schema = Schema.of(name, postAbbr);
        // Skip header row in the file.
        CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
        // Set job options
        LoadJobConfiguration loadConfig = LoadJobConfiguration.newBuilder(table.getTableId(), sourceUri).setSchema(schema).setFormatOptions(csvOptions).setSchemaUpdateOptions(ImmutableList.of(JobInfo.SchemaUpdateOption.ALLOW_FIELD_RELAXATION)).setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND).build();
        // Create a load job and wait for it to complete.
        Job job = bigquery.create(JobInfo.of(loadConfig));
        job = job.waitFor();
        // Check the job's status for errors
        if (job.isDone() && job.getStatus().getError() == null) {
            System.out.println("Relax column append successfully loaded in a table");
        } else {
            System.out.println("BigQuery was unable to load into the table due to an error:" + job.getStatus().getError());
        }
    } catch (BigQueryException | InterruptedException e) {
        System.out.println("Column not added during load append \n" + e.toString());
    }
}

Example 2 with CsvOptions

use of com.google.cloud.bigquery.CsvOptions in project java-bigquery by googleapis.

the class QueryExternalGcsPerm method queryExternalGcsPerm.

public static void queryExternalGcsPerm(String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
        // Initialize client that will be used to send requests. This client only needs to be created
        // once, and can be reused for multiple requests.
        BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
        // Skip header row in the file.
        CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
        TableId tableId = TableId.of(datasetName, tableName);
        // Create a permanent table linked to the GCS file
        ExternalTableDefinition externalTable = ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
        bigquery.create(TableInfo.of(tableId, externalTable));
        // Example query to find states starting with 'W'
        TableResult results = bigquery.query(QueryJobConfiguration.of(query));
        results.iterateAll().forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));
        System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
        System.out.println("Query not performed \n" + e.toString());
    }
}

Example 3 with CsvOptions

use of com.google.cloud.bigquery.CsvOptions in project java-bigquery by googleapis.

the class QueryExternalGcsTemp method queryExternalGcsTemp.

public static void queryExternalGcsTemp(String tableName, String sourceUri, Schema schema, String query) {
    try {
        // Initialize client that will be used to send requests. This client only needs to be created
        // once, and can be reused for multiple requests.
        BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
        // Skip header row in the file.
        CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
        // Configure the external data source and query job.
        ExternalTableDefinition externalTable = ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
        QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).addTableDefinition(tableName, externalTable).build();
        // Example query to find states starting with 'W'
        TableResult results = bigquery.query(queryConfig);
        results.iterateAll().forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));
        System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
        System.out.println("Query not performed \n" + e.toString());
    }
}

Example 4 with CsvOptions

use of com.google.cloud.bigquery.CsvOptions in project java-bigquery by googleapis.

the class CreateExternalTableAwsIT method testCreateExternalTableAws.

@Test
public void testCreateExternalTableAws() {
    String sourceUri = "s3://omni-samples-test-bucket/us-states.csv";
    Schema schema = Schema.of(Field.of("name", StandardSQLTypeName.STRING), Field.of("post_abbr", StandardSQLTypeName.STRING));
    CsvOptions options = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
    ExternalTableDefinition externalTableDefinition = ExternalTableDefinition.newBuilder(sourceUri, options).setConnectionId(AWS_READ_CONNECTION_ID).setSchema(schema).build();
    CreateExternalTableAws.createExternalTableAws(OMNI_PROJECT_ID, OMNI_DATASET_NAME, tableName, externalTableDefinition);
    assertThat(bout.toString()).contains("Aws external table created successfully");
}

Example 5 with CsvOptions

use of com.google.cloud.bigquery.CsvOptions in project jade-data-repo by DataBiosphere.

the class BigQueryPdao method buildFormatOptions.

private FormatOptions buildFormatOptions(IngestRequestModel ingestRequest) {
    FormatOptions options;
    switch(ingestRequest.getFormat()) {
        case CSV:
            CsvOptions csvDefaults = FormatOptions.csv();
            options = CsvOptions.newBuilder().setFieldDelimiter(ingestRequest.getCsvFieldDelimiter() == null ? csvDefaults.getFieldDelimiter() : ingestRequest.getCsvFieldDelimiter()).setQuote(ingestRequest.getCsvQuote() == null ? csvDefaults.getQuote() : ingestRequest.getCsvQuote()).setSkipLeadingRows(ingestRequest.getCsvSkipLeadingRows() == null ? csvDefaults.getSkipLeadingRows() : ingestRequest.getCsvSkipLeadingRows()).setAllowQuotedNewLines(ingestRequest.isCsvAllowQuotedNewlines() == null ? csvDefaults.allowQuotedNewLines() : ingestRequest.isCsvAllowQuotedNewlines()).build();
            break;
        case JSON:
            options = FormatOptions.json();
            break;
        default:
            throw new PdaoException("Invalid format option: " + ingestRequest.getFormat());
    }
    return options;
}