Use Analytics BigQuery

What is BigQuery?

Google BigQuery is a fully managed enterprise data warehouse that allows you to manage and analyze data provided by Google. It is a service that can process petabytes of data very using SQL query.

Logs sent to Hive Analytics through Hive Analytics can be processed using Google BigQuery.

 

You can use BigQuery in Hive Analytics through the following procedure.

  • Create a Google account
  • Apply for permission for BigQuery
  • Access and use BigQuery

Create a Google Account

A Google Gmail account is required to access Hive Analytics BigQuery. You can access Google to create one.

 

Apply for Permission for BigQuery

Access to BigQuery in Hive Analytics requires a simple application process.

The Hive console administrator can apply by creating a Google Gmail account and using theBigQuery access function in the Hive console > Hive Analytics > Log Definition.

Click the BigQuery access button, enter your Gmail account information, and then click the Apply button to grant access instantly.

Cannot grant permission on invalid gmail addresses.

 

Use BigQuery

Things to Check for the Initial BigQuery Access

To utilize Analytics BigQuery, at least one Google Cloud must be created.

First-time users can create a Google project and access the sandbox environment using Google’s BigQuery Sandbox use guide.

It is possible to search BigQuery data up to the free usage limit with the BigQuery Sandbox environment.

A BigQuery upgrade is required if you exceed the quota or feature limitations.

 

 

Access BigQuery

You can access Analytics BigQuery using the BigQuery Access function in the Hive Console > Hive Analytics > Log Definition if a BigQuery project exists and the Analytics BigQuery access permission has been granted.

Then, after accessing Hive Analytics BigQuery and completing the basic settings by performing the following, you will be able to query logs by executing SQL.

 

Add BigQuery to Favorites

The Favorites feature in BigQuery, where analytics data is kept, makes it simple to access datasets.

Click “Star Project by Name” after clicking the “Add” button in the top left corner.

After that, type fluted-airline-109810 in the pop-up window and click the “star” button.

The fluted-airline-109810 project has been added to the BigQuery console’s left explorer. The arrow button exposes accessible datasets, allowing you to see tables and information.

 

 

Launch BigQuery SQL

BigQuery Job user permission is required separately in the project in order to execute Bigquery’s SQL. However, if you use a project developed in the BigQuery Sandbox environment, you can search up to the free usage limit without setting additional permissions.

If you require SQL processing in excess of the free usage limit, you can use BigQuery’s upgrade.

 

If more than one Google project has been created, an executable project is automatically set when you access BigQuery; you can verify it as follows.

You can set the project in the following method if you do not see it as shown in the image above.

First, click the red square where the project is exposed to begin. Next, enter the Google project name that you just created. The user enters the Google project name, and enters the Google project to which BigQuery Job user rights have been granted.

 

Click the “+” button to create one SQL Editor window and create a query. Click the triangular button on the left of fluted-airline-109810 to view accessible lists.

You can view logs by entering the sample query below in the SQL data window and pressing the Run button.

 

 

SQL Note

  • The FROM paragraph should be in a form of fluted-airline-109810.analytics_Number_live.Table Name. Login Log Inquiry Sample Query

Login Log Inquiry Sample Query

SELECT datetime, appid, guid
FROM fluted-airline-109810.analytics_77777_live.t_hive_login_log
WHERE DATE(dateTime) = date(datetime_add(current_datetime('Asia/Seoul'),interval -9 hour))
LIMIT 1

 

Upload data to Google BigQuery and JOIN with Hive Analytics BigQuery data

You can run SQL by JOIN with the Analytics log table if you create a Google BigQuery project and upload data. However, the data location for BigQuery dataset creation must be set to the US (multi-region in the United States).

Order

  • Create Google BigQuery Project
  • Create BigQuery Data Set to US Region
  • Upload Data
  • JOIN with Analytics BigQuery Data

Create Google BigQuery Project

 

Create BigQuery Data Set to US Region

  • Access the BigQuery Console, select US (multi-region in the United States) as the data location at the data set creation section, and click “Create Data Set”.

Create Table and Upload Data

  • Create a table by referring to the table creation guide in the created dataset.
  • Upload data to the created table by referring to Table Data Management. Typically, there is a case of uploading a CSV file or saving query results as a table.

 

Set Up SQL Launch Project

  • Select the COM2US.COM organization in the pop-up that appears when you click C2S-DW in the upper left corner.
  • Select and click on one project starting with “habq-”.

 

JOIN with Analytics BigQuery Data

  • Enter a query in the SQL editor window and click the Execute button to execute the SQL.

 

Sample query. Analytics login log and table JOIN lookup uploaded to the BigQuery project

SELECT a.vid, b.vid
FROM fluted-airline-109810.analytics_7777_live.t_hive_login_log a
inner join tribal-booth-366804.test.upload_test b on b.vid = a.vid
WHERE DATE(dateTime) = date(datetime_add(current_datetime('Asia/Seoul'),interval -9 hour))

 

Apply for BigQuery Permission Recovery

You can ask for withdrawal if you have been assigned to Hive Analytics BigQuery after applying for permission.

You can view the previously requested access permissions by using the bigquery access feature in the Hive Console > Hive Analytics > Log Definition Menu. By choosing the account that needs its permissions revoked and clicking the Revoke Permissions button, you may submit your application.

Processing may take up to 2 business days, and the result will be sent to the email address of the request account upon successful revocation.