<jupyter_start><jupyter_text>Prototype differential analsysTools to compute and compare table or view summaries<jupyter_code>import re
import pandas as pd
import BQhelper as bq
import inspectTable

bq.project = "mlab-sandbox"

# Replicate/audit inventories from 2020/02/28

# measurement-lab.ndt.downloads as Inventory_measurement_lab_ndt_downloads
# measurement-lab.ndt.uploads AS Inventory_measurement_lab_ndt_uploads
# mlab-oti.base_tables.ndt AS Inventory_mlab_oit_base_tables

basicIQ="""
WITH

CurrTab AS (
    SELECT
      CAST(log_time AS DATE) as test_date,
      REPLACE(REGEXP_EXTRACT(task_filename, "-(mlab[1-4]-[a-z][a-z][a-z][0-9][0-9t])-"), "-", ".") AS hostname,
      connection_spec.data_direction AS direction,
      COUNT( DISTINCT test_id) AS current_tests
    FROM `{table}`
    GROUP BY test_date, hostname, direction
),

RawData AS (
SELECT * FROM {table} LIMIT 100
),

deltaTab AS (
    SELECT *, CurrTab.current_tests-legacy_tests AS delta
    FROM CurrTab
    JOIN {archivedTable} AS archive
    USING (test_date, hostname, direction)
    WHERE CurrTab.current_tests != archive.legacy_tests
)


# select * FROM DeltaTab LIMIT 10
SELECT
    SUM(legacy_tests) AS legacy,
    SUM(current_tests) AS cur,
    SUM(delta) AS netD,
    SUM(delta) / SUM(legacy_tests) AS ratio
FROM deltaTab
"""

r=bq.DataFrameQuery(basicIQ, table='mlab-oti.base_tables.ndt', archivedTable='mlab-sandbox.mattmathis.Inventory_mlab_oit_base_tables')
print (r)<jupyter_output>legacy       cur    netD    ratio
0  54566100  54443863 -122237 -0.00224
{'fraction_non_alphanumeric': 0.09831824062095731, 'fraction_numerical': 0.031047865459249677, 'mean_word_length': 4.46643109540636, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '207030', 'license': 'Apache-2.0', 'n_tokens_mistral': 606, 'n_tokens_neox': 549, 'n_words': 126, 'path': 'inspector/plottools/Prototype differential analysis.ipynb', 'repo_name': 'm-lab/analysis', 'size': '1546'}