Please enable JavaScript.

Coggle requires JavaScript to display documents.

BigQuery - Coggle Diagram

- - - - Cols stored in separated file block
      - 1 capacitor file = all cols
      - encryption + compression
      - column value + definition + repetition
      - Encoding
        
        Run-length
        
        Dictionary
  - - - modified in < 90 days, priced as X
    - - modified in >90 days, priced as X/2
  - - - Scan less data on disk by slot workers!
      - Types
        
        Ingestion time
        
        same as INT part. with:
        _PARTITION(TIME/DATE) pseudo cols
        
        DATE / TIMESTAMP COL
        
        same as INT partitioning, but with time cols
        
        INTEGER col
        
        range of int values is considered a partition
        
        __UNPARTITIONED__ & __NULL__ special partitions
      - Granularity
        
        Hourly
        
        Daily
        
        Yearly
        
        Yearly
    - - Def: storing data on disk in a sorted way,
        determined by <= 4 cols (sorting in -> direction)
        NOTE: sort order is always valid and
        updated automatically
        
        Automatic re-clustering (VACUUM in Redshift)
        (works ~LSM tree (log-structured merge tree) -
        DS, ~ BST with C0 - tree, kept in memory
        and C1 - analogous T, kept on disk
        NOTE: this thresh in BQ is 500G (28.06.21)
        
        Clustering op = block level pruning and scans only data relevant to the query reducing the number of bytes processed by the query
      - Used for high-cardinality &
        non-temporal cols
        Prevents scanning unnec. rows
      - Makes difference when some cols are
        used for agg / querying more than others
      - works independently of T partitioning
- - - - Repairing inefficient file constructions.
      - Automatically fixing the many small files problem common in many Big Data systems.
      - Rectifying difficult data shapes and data skew, and caching data & metadata for performance reasons.
- - - - insert rows to source if not found
      - update rows if found
      - throw error if matched more than 1 row!
      - possible to delete rows in source
  - - - It means: All reads from a transaction will
        see a consistent DB state and the transaction itself will successfully commit only if no updates it has made conflict with any concurrent updates made since that snapshot.