howto:sql_table:defined_generated

About

This how-to will show you how to:

define the generation of data via a generator
and load it into a table

Steps

Prerequisites

You should have Tabulify installed on your computer.

Create the Data Generation file

The below file is a resource generator that defines the structure of the data and the data suppliers

kind: generator
spec:
  Comment: An example of date dimension generator based on the `date_dim` table of TPCDS
  primary-columns: [ "d_date_sk" ]
  Columns:
    - name: d_date_sk
      comment: A surrogate key
      Type: integer
      data-supplier:
        type: sequence
    - name: d_date
      comment: A business key in date format
      Type: date
      data-supplier:
        type: sequence
        arguments:
          start: 2025-05-13
    - name: d_date_id
      comment: A business key in string
      Type: varchar
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.toISOString().substring(0,10)"
    - name: d_month_seq
      comment: An ascendant sequence for the month
      Type: integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "function pad(number) {if (number < 10) { return '0' + number; } return number; }; d_date.getFullYear()+''+(pad(d_date.getMonth()+1))"
    - name: d_day_name
      comment: The name of the day
      Type: varchar
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday']; days[d_date.getDay()]"
    - name: d_moy
      comment: the month number in year
      Type: Integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.getMonth()+1"
    - name: d_year
      comment: The year number
      Type: Integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.getFullYear()"

Drop the target table if it exists

With tabul drop, we will:

drop the date_dim table from sqlite
not fail if there is no table found
drop the foreign keys thanks to the --force if any exists

tabul data drop --no-strict-selection --force date_dim@sqlite

Fill the table

The tabul data fill command takes the data definition as input and can load it into a table.

tabul data upsert date_dim--generator.yml@howto date_dim@sqlite

Transfer results
input             target            latency   record_count   error_code   error_message
---------------   ---------------   -------   ------------   ----------   -------------
date_dim@memgen   date_dim@sqlite   0.888s             100

Verify

The table date_dim was filled with generated data

tabul data head date_dim@sqlite