Scenario: Performing a query in BigQuery - 6.3

Talend Open Studio for Big Data Components Reference Guide

EnrichVersion
6.3
EnrichProdName
Talend Open Studio for Big Data
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

This scenario uses two components to perform the SELECT query in BigQuery and present the result in the Studio.

The following figure shows the schema of the table, UScustomer, we use as example to perform the SELECT query in.

We will select the State records and count the occurrence of each State among those records.

Linking the components

  1. In the Integration perspective of Studio, create an empty Job, named BigQueryInput for example, from the Job Designs node in the Repository tree view.

    For further information about how to create a Job, see the Talend Studio User Guide.

  2. Drop tBigQueryInput and tLogRow onto the workspace.

  3. Connect them using the Row > Main link.

Creating the query

Building access to BigQuery

  1. Double-click tBigQueryInput to open its Component view.

  2. Click Edit schema to open the editor

  3. Click the button twice to add two rows and enter the names of your choice for each of them in the Column column. In this scenario, they are: States and Count.

  4. Click OK to validate these changes and accept the propagation prompted by the pop-up dialog box.

  5. Navigate to the Google APIs Console in your web browser to access the Google project hosting the BigQuery and the Cloud Storage services you need to use.

  6. Click the API Access tab to open its view.

  7. In the Component view of the Studio, paste Client ID, Client secret and Project ID from the API Access tab view to the corresponding fields, respectively.

Getting Authorization code

  1. In the Run view of the Studio, click Run to execute this Job. The execution will pause at a given moment to print out in the console the URL address used to get the authorization code.

  2. Navigate to this address in your web browser and copy the authorization code displayed.

  3. In the Component view of tBigQueryInput, paste the authorization code in the Authorization Code field.

Writing the query

  • In the Query field, enter

    select States, count (*) as Count from documentation.UScustomer group by States

Executing the Job

The tLogRow component presents the execution result of the Job. You can configure the presentation mode on its Component view.

To do this, double-click tLogRow to open the Component view and in the Mode area, select the Table (print values in cells of a table) option.

  • To execute this Job, press F6.

Once done, the Run view is opened automatically, where you can check the execution result.