gingerwizard

Create the Table

Optionally remove the secondary indices.

CREATE TABLE otel_traces
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
 `SpanId` String CODEC(ZSTD(1)),

gingerwizard

default:
  # list of environment variables applied to all components
  env:
    - name: OTEL_SERVICE_NAME
      valueFrom:
        fieldRef:
          apiVersion: v1
          fieldPath: "metadata.labels['app.kubernetes.io/component']"
    - name: OTEL_K8S_NAMESPACE
      valueFrom:

gingerwizard

import logging
import sys
import time
import clickhouse_connect
from boto3 import Session

logger = logging.getLogger()
logger.setLevel(logging.INFO)

handler = logging.StreamHandler(sys.stdout)

gingerwizard

Introduction

The following provides the details required to reproduce the demo given on the Real-time SQL analytics at scale: A story of open-source GitHub activity using ClickHouse + Grafana webinar.

This provides a Grafana dashboard showing an analysis of the history of the Grafana repository, including common commiters, most popular issues, largest commits and files with the longest history.

gingerwizard

gingerwizard

gingerwizard

CREATE TABLE `bigquery-public-data.crypto_ethereum.contracts`
(
  address STRING NOT NULL OPTIONS(description="Address of the contract"),
  bytecode STRING OPTIONS(description="Bytecode of the contract"),
  function_sighashes ARRAY<STRING> OPTIONS(description="4-byte function signature hashes"),
  is_erc20 BOOL OPTIONS(description="Whether this contract is an ERC20 contract"),
  is_erc721 BOOL OPTIONS(description="Whether this contract is an ERC721 contract"),
  block_timestamp TIMESTAMP NOT NULL OPTIONS(description="Timestamp of the block where this contract was created"),
  block_number INT64 NOT NULL OPTIONS(description="Block number where this contract was created"),

gingerwizard

SELECT
    sum(sum_tx_ether),
    sum(avg_tx_gas_cost)
FROM
(
    SELECT
        SUM(value / POWER(10, 18)) AS sum_tx_ether,
        AVG(gas_price * (receipt_gas_used / POWER(10, 18))) AS avg_tx_gas_cost,
        toStartOfDay(block_timestamp) AS tx_date

gingerwizard

Blocks

CREATE TABLE ethereum.blocks
(
    `number` UInt32 CODEC(Delta(4), ZSTD(1)),
    `hash` String,
    `parent_hash` String,
    `nonce` String,
    `sha3_uncles` String,

gingerwizard

CREATE TABLE `clickhouse-cloud.crypto_ethereum.traces`
(
  transaction_hash STRING OPTIONS(description="Transaction hash where this trace was in"),
  transaction_index INT64 OPTIONS(description="Integer of the transactions index position in the block"),
  from_address STRING OPTIONS(description="Address of the sender, null when trace_type is genesis or reward"),
  to_address STRING OPTIONS(description="Address of the receiver if trace_type is call, address of new contract or null if trace_type is create, beneficiary address if trace_type is suicide, miner address if trace_type is reward, shareholder address if trace_type is genesis, WithdrawDAO address if trace_type is daofork"),
  value NUMERIC OPTIONS(description="Value transferred in Wei"),
  input STRING OPTIONS(description="The data sent along with the message call"),
  output STRING OPTIONS(description="The output of the message call, bytecode of contract when trace_type is create"),