BigQuery Extension

BigQuery is a fully-managed, serverless data warehouse that enables scalable analysis over petabytes of data.

Nexus provides an opportunity with connecting to BigQuery data base with rapid point-to-point integration by providing built in extension which is just few clicks away.

Storing and querying massive datasets can be time consuming and expensive without the right hardware and infrastructure. You can control access to both the project and your data based on your business needs, such as giving others the ability to view or query your data through Nexus.

Nexus as an integration tool that solves this problem by enabling super-fast SQL queries using the processing power of Google's infrastructure. Simply move your data into BigQuery and let Nexus handle the hard work.

You can access BigQuery by using the Nexus built-in extension , using the connector, services and operations offered by the NEXUS platform, or by making calls to the BigQuery SQL Engine with custom defined query.

Nexus provides below built in extension to query database.

• Table

  • Get Rows

  • Get Description

  • Create Table

  • Insert

• SQL

Lets understand how to use Nexus for Get Rows operation.

Get Rows functionality helps the user to get the rows out of the table without much worrying about writing SQL query. The process just requires you to add service in connector and select operation as Get Rows.

Below slides will help the user to set up Nexus platform to use the Get Rows:

⏩

To set up connector for BigQuery , select the option Google BigQuery from the drop down of extension tab.

⏩

To add Service click to "+Add Service" button and provide title to the service.

The Name tab requires the "Name Of The Table" , to be queried.

⏩

To add Operations click to "+Add Operation" button and provide title to the operation.

In the drop down of Name tab , among the operation available , you can choose any.

Here "Get Rows" is selected to query the table and to get the rows from BigQuery database.

⏩

Provide variable in the output tab.

⏩

Map the response mapping.

for entirely selecting the response, use "*"

⏩

Paging through table data: The number of results through Get Rows is controlled by themaxResults property.

⏩

Upon configuring the flow in Designer.

The run will lead you to the output where you will be able to see the rows from the table queried.

Lets understand how to use Nexus for Get Description operation.

Get Description helps the user to get essential details about the Table as shown below.

Request:
Response:
creationTime: "1614601721652"
description: "Gaea Sales Details"
etag: "MsArHxzhsCx84C6mg0z8kA=="
expirationTime: "1615206521652"
id: "exto-**********:nexus_ext_development.sales_details_duplicate"
kind: "bigquery#table"
labels:
product: "nexus"
lastModifiedTime: "1614606946504"
location: "US"
numBytes: "22"
numLongTermBytes: "0"
numRows: "1"
schema:
fields:
0:
name: "Region"
type: "STRING"
1:
name: "Country"
type: "STRING"
2:
name: "Item_Type"
type: "STRING"
selfLink: "https://bigquery.googleapis.com/bigquery/v2/projects/exto-***********/datasets/nexus_ext_development/tables/sales_details_duplicate"
tableReference:
datasetId: "nexus_ext_development"
projectId: "exto-************"
tableId: "sales_details_duplicate"
type: "TABLE"

Below slides will facilitate the user to use Get Description.

⏩

set up connector for BigQuery , select the option Google BigQuery from the drop down of extension tab.

⏩

To add Service click to "+Add Service" button and provide title to the service.

The Name tab requires the "Name Of The Table" , to be queried.

⏩

Here "Get Description" is selected to query the table and to get the description about the table.

⏩

For setting up the flow in designer, select the service and operation and provide a variable for Output.

⏩

Here is the snap of the result of the operation .

Lets understand how to use Nexus to Create Table operation.

A label is a key-value pair that helps you organize your Google Cloud BigQuery resources. You can attach a label to each resource, then filter the resources based on their labels. Information about labels is forwarded to the billing system, so you can break down your billing charges by label.

return {
  "product":"nexus"
}

Each label must be a key-value pair. Keys have a minimum length of 1 character and a maximum length of 63 characters, and cannot be empty. Values can be empty, and have a maximum length of 63 characters. Keys and values can contain only lowercase letters, numeric characters, underscores, and dashes

⏩

The Name tab requires the "Name Of The Table" , to be created.

Select type "Table" from the Type dropdown.

To add Operation click to "+Add operation" button and provide title to the operation.

⏩

Select operation "Create Table " from the Name drop down.

Add columns details for the table to be created like name ,data type.

⏩

In Flow, Select the service created in the previous step and operation name.

User can provide Labels to the table as per the below example.

⏩

User can provide Description to the table as below:

⏩

Below is the response for the create table action flow.

Lets understand how to use Nexus for Insert Table operation.

Inserting into table comes so effortlessly with Nexus. It just requires to select Target field(Column Name) and value to be entered in the Source field.

	Source field	               Target field		
  "Asia"                       Region
  "India"                      Country

Below slides will facilitate in using Insert Table operation of Nexus

⏩

After adding the service , Click to button "+Add Operation" to add operation.

Choose operation "Insert" from the Name drop down.

⏩

In Flow, Choose the service and operation created in the previous step.

It's mandatory to provide value mapping for the columns with the data to be entered against each columns as below manner:

⏩

Map the response mapping

⏩

Below is the result of Insert Operation.

Lets understand how to use Nexus to write SQL query for BigQuery

Functionality of Nexus providing an opportunity to write Custom Defined Query makes it agile in terms of workspace. Use custom queries to apply user-defined values to dimensions, measures, and limits of other queries. eg.

SELECT  *  FROM `exto-*********.nexus_ext_development.sales_details_duplicate` LIMIT 1000

Below slides will help you to set up Nexus platform to use SQL custom defined query :

⏩

While creating service choose "SQL" from the Type drop down.

Click on "+Add Operation" to add operation to the service.

⏩

Nexus provides an opportunity to write Custom Defined Query for BigQuery data base in the console provided as you can see below:

⏩

Select the service and operation in the Flow

⏩

Below is the response for Select SQL query .