n0531m

function getBqTempTableByJobid {
  local JOBID=$1
  bq show --format json -j ${JOBID} \
    | jq -r '.configuration.query.destinationTable | .projectId +":"+.datasetId+"."+.tableId'
}

n0531m

#!/bin/bash

gcloud --project deeplearning-platform-release \
  compute images list --no-standard-images 

n0531m

#DECLARE #  PRECISION INT64 DEFAULT 6;
DECLARE PRECISION_GEOHASH INT64 DEFAULT 2;

WITH # first table : only aggregating the rows with exact match on     country_region, province_state, longitude, latitude
 # also rounding lat/lng  to a set precision. (defined in declard param)
 # also adding a geohash value based point point
 t1 AS
    ( SELECT CASE
                 WHEN country_region IN ("South Korea",
                                         "Korea, South",

n0531m

#
# https://en.wikipedia.org/wiki/Radian
# https://towardsdatascience.com/calculating-the-bearing-between-two-geospatial-coordinates-66203f57e4b4

CREATE TEMP FUNCTION 
  PI (degree float64) AS (ACOS(-1));

CREATE TEMP FUNCTION
  RADIANS (degree FLOAT64) AS (degree * ACOS(-1) / 180);
CREATE TEMP FUNCTION

n0531m

Overview

• this is my quick note of attempting to load Indonesia's admin boundaries into BigQuery

Source

• Indonesia - Subnational Administrative Boundaries Humanitarian Data Exchange
  • License : Creative Commons Attribution for Intergovernmental Organisations (CC BY-IGO) https://data.humdata.org/about/license

Good pre-read :

• How to load geographic data like shapefiles into BigQuery (by Lak)

n0531m

##!/bin/bash

## setting up the least to a clean linux env on a chromebook
## mostly a copy&paste from instructions found on the web, which is why the approach is not consistent...
## i might clean it up in the future but as long as it works it does the job.

sudo apt-get update
sudo apt-get upgrade

sudo apt-get install apt-transport-https ca-certificates gnupg software-properties-common apt-utils

n0531m

Extract metadata from Data Studio

this is some example code I used to extract metadata (REPORT/DATA_SOURCE) from Data Studio.

Warning : Information provided via Data Studio API very limited. So if you are looking for ways to understand more details such as what are the constructs of a REPORT or what backend a DATA_SOURCE is connecting to, unfortunately these will not help. That said, it can still be better than nothing so that you know what assets are existing within the organization.

Extracted information can be potentially cataloged in some other services such as Data Catalog. However, that is beyond what this sample covers.

products in use

n0531m

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <groupId>com.gmail.n0531m</groupId>
   <artifactId>mysql_${mysql.version}-with-cloudsql-socket-factory_${cloudsql.socketfactory.version}</artifactId>
   <packaging>pom</packaging>
   <version>0.0.1</version>
   <prerequisites>
      <maven>3.6.0</maven>
   </prerequisites>

n0531m

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.gmail.n0531m</groupId>
  <artifactId>postgresql_${postgresql.version}-with-cloudsql-socket-factory_${postgresql.socketfactory.version}</artifactId>
  <packaging>pom</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <prerequisites>
    <maven>3.6.0</maven>
  </prerequisites>

n0531m

#!/bin/bash 

##Source : Cloud SQL Instance/Table/SQL for extraction
CLOUDSQL_INSTANCE_PROJECT=imfeelinglucky-utilities
CLOUDSQL_INSTANCE_REGION=asia-southeast1
CLOUDSQL_INSTANCE_NAME=testmysql57
CLOUDSQL_INSTANCE_USERNAME=
CLOUDSQL_INSTANCE_USERPASS=
CLOUSSQL_SQL="select * from mysql.user"