Loading Public Data into Big Query

  • date 29th May, 2021 |
  • by Prwatech |
  • 0 Comments

Accessing Public Datasets in BigQuery

 

Prerequisites

 

GCP account

Open the Cloud Console.

Open Menu > Big Query > SQL Workspace

Click on Add Data > Explore Public Data sets.

Type NYC bike.

It will show New York citi bike trips data. Click it.

Click on View dataset.

The dataset will be added.

Expand the added data set

Goto new_york_citibike > citibike_trips

Click on Schema. It will show the schema of table.

Click on Details. It will show the details of the  Table.

Click on Preview. It will show the table.

Click on Compose new Query.

Paste the below code in query editor.

SELECT

   MIN(start_station_name) AS start_station_name,

   MIN(end_station_name) AS end_station_name,

   COUNT(tripduration) AS num_trips

FROM

   bigquery-public-data.new_york_citibike.citibike_trips

WHERE

   start_station_id != end_station_id

GROUP BY

   start_station_id,

   end_station_id

ORDER BY

   num_trips DESC

LIMIT

   10

Click RUN

It will show the Results of query.

Click on Query history. It will show the query history.

Click on Job Information.

It will show the job description.

Click on Execution Details. It will Show the execution details

To save Query,

Click on Save Query button.

Give the query name.

Choose the visibility mode. Click Save.

To see the Query settings, Click on More > Query Settings.

In Query settings, you can choose the engine for query.

You can change the destination for your query result. Instead of temporary table, you can choose a table in the project itself.

Select the destination table for query dataset.

Choose the project and dataset.

Give the table name and write preference.

Click Save.

Write the below query.

WITH

   trip_distance AS (

SELECT

   bikeid,

   ST_Distance(ST_GeogPoint(s.longitude,s.latitude),

   ST_GeogPoint(e.longitude,e.latitude)) AS distance

FROM

   bigquery-public-data.new_york_citibike.citibike_trips,

   bigquery-public-data.new_york_citibike.citibike_stations as s,

   bigquery-public-data.new_york_citibike.citibike_stations as e

WHERE

   start_station_id = s.station_id

   AND end_station_id = e.station_id)

SELECT

   bikeid,

   SUM(distance)/1000 AS total_distance

FROM

   trip_distance

GROUP BY

   bikeid

ORDER BY

   total_distance DESC

LIMIt

   5

Click Run.

The result of query will be displayed in query results. It will be saved into new table named table_query_result

SELECT

   wx.date,

   wx.value/10.0 AS prcp

FROM

   bigquery-public-data.ghcn_d.ghcnd_2015 AS wx

WHERE

   id = ‘USW00094728’

   AND qflag IS NULL

   AND element = ‘PRCP’

   ORDER BY

   wx.date

Click Run.

The query results will be displayed.

0
0

Quick Support

image image