気軽にData Observabilityを導入できる「Soda」のチュートリアルをやってみた

$ pip install soda-sql-postgresql

$ git clone https://github.com/sodadata/tutorial-demo-project.git
$ cd tutorial-demo-project/
$ docker-compose up -d

$ cd workspace/new_york_bus_breakdowns_demo/

$ soda create postgres
  | Soda CLI version 2.1.0b22
  | Warehouse file warehouse.yml already exists
  | Adding env vars for None to /Users/haruta.takumi/.soda/env_vars.yml
  | Review warehouse.yml by running command
  |   cat warehouse.yml
  | Review section None in ~/.soda/env_vars.yml by running command
  |   cat ~/.soda/env_vars.yml
  | Then run the soda analyze command
  | Starting new HTTPS connection (1): collect.dev.sodadata.io:443
  | https://collect.dev.sodadata.io:443 "POST /v1/traces HTTP/1.1" 200 0

name: sodasql
connection:
  type: postgres
  host: localhost
  port: "5432"
  username: sodasql
  password: env_var(POSTGRES_PASSWORD)
  database: sodasql_tutorial
  schema: new_york

postgres:
  POSTGRES_USERNAME: Eg johndoe
  POSTGRES_PASSWORD: Eg abc123

null:
  POSTGRES_USERNAME: Eg johndoe
  POSTGRES_PASSWORD: Eg abc123

$ soda analyze
  | 2.1.0b22
  | Analyzing warehouse.yml ...
  | Querying warehouse for tables
  | Directory tables already exists
  | Executing SQL query: 
SELECT table_name 
FROM information_schema.tables 
WHERE lower(table_schema)='new_york'
  | SQL took 0:00:00.006497
  | Executing SQL query: 
SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE lower(table_name) = 'breakdowns' 
  AND table_catalog = 'sodasql_tutorial' 
  AND table_schema = 'new_york'
  | SQL took 0:00:00.007527
  | Executing SQL query: 
SELECT 
  COUNT(CASE WHEN "school_year" ~* '^\-?[0-9]+$' THEN 1 END) AS number_whole,

...

  | SQL took 0:00:00.015954
  | Creating tables/breakdowns.yml ...
  | Next run 'soda scan warehouse.yml tables/breakdowns.yml' to calculate measurements and run tests
  | Starting new HTTPS connection (1): collect.dev.sodadata.io:443
  | https://collect.dev.sodadata.io:443 "POST /v1/traces HTTP/1.1" 200 0
$ ls tables
breakdowns-demo.yml breakdowns.yml

table_name: breakdowns
metrics:
  - row_count
  - missing_count
  - missing_percentage
  - values_count
  - values_percentage
  - invalid_count
  - invalid_percentage
  - valid_count
  - valid_percentage
  - avg_length
  - max_length
  - min_length
  - avg
  - sum
  - max
  - min
  - stddev
  - variance
tests:
  - row_count > 0
columns:
  school_year:
    valid_format: date_inverse
    tests:
      - invalid_percentage == 0
  bus_no:
    valid_format: number_whole
    tests:
      - invalid_percentage <= 20
  schools_serviced:
    valid_format: number_whole
    tests:
      - invalid_percentage <= 15

$ soda scan warehouse.yml tables/breakdowns.yml
  | 2.1.0b22
  | Scanning tables/breakdowns.yml ...
  | Environment variable POSTGRES_PASSWORD is not set
  | There is no value specified for valid_values for column school_year
  | There is no value specified for valid_min for column school_year
  | There is no value specified for valid_max for column school_year
  | There is no value specified for valid_values for column bus_no

...

  | Test column(schools_serviced) test(invalid_percentage <= 15) passed with measurements {"expression_result": 12.095620956209562, "invalid_percentage": 12.095620956209562}
  | Executed 2 queries in 0:00:00.751460
  | Scan summary ------
  | 239 measurements computed
  | 4 tests executed
  | All is good. No tests failed.
  | Exiting with code 0
  | Starting new HTTPS connection (1): collect.dev.sodadata.io:443
  | https://collect.dev.sodadata.io:443 "POST /v1/traces HTTP/1.1" 200 0

name: sodasql
connection:
  type: postgres
  host: localhost
  port: "5432"
  username: sodasql
  password: env_var(POSTGRES_PASSWORD)
  database: sodasql_tutorial
  schema: new_york
soda_account:
  host: cloud.soda.io
  api_key_id: <PUBLIC KEY>
  api_key_secret: <PRIVATE KEY>

$ soda scan warehouse.yml tables/breakdowns.yml

soda scan warehouse.yml tables/breakdowns.yml