---json
{
    "aliases": [
        { "path": ":howto:sqlite:defined_generated_data" }
    ],
    "description": "This how-to will show you how to define the generation of data via a data definition file and load it into a table via the tabul data fill operation.",
    "low_quality_page": "false",
    "name": "Fill a table with Defined Data Generator",
    "page_id": "lvcdrwjg8g0x0q2xsiy6c"
}
---
====== SQLite - How to fill a table with a resource data generator? ======

===== About =====
This [[..:howto|how-to]] will show you how to:
  * define the <icon name="mdi:cog"/> [[docs:generator:generator|generation of data]] via a [[:docs:resource:generator|generator]]
  * and [[docs:op:copy|load it]] into a <icon name="mdi:table-large" /> [[docs:resource:sql_table|table]]

===== Steps =====
==== Prerequisites ====
You should have ''Tabulify'' [[howto:getting_started:1_install|installed on your computer]].
==== Create the Data Generation file ====
The below file is a [[:docs:resource:generator|resource generator]] that defines the structure of the data and the [[:docs:generator:data-supplier|data suppliers]]
<unit>
<file yaml date_dim--generator.yml>
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()"

</file>
</unit>


==== Drop the target table if it exists ====


With [[:docs:tabul:data:drop|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

<unit>
<code bash>
tabul data drop --no-strict-selection --force date_dim@sqlite
</code>
</unit>

==== Fill the table ====
The [[docs:tabul:data:fill|tabul data fill]] command takes the data definition as input and can load it into a table.
<unit>
<code bash>
tabul data upsert date_dim--generator.yml@howto date_dim@sqlite
</code>
<console>
Transfer results
input             target            latency   record_count   error_code   error_message
---------------   ---------------   -------   ------------   ----------   -------------
date_dim@memgen   date_dim@sqlite   0.888s             100
</console>
</unit>
==== Verify ====

The table ''date_dim'' was filled with generated data
<unit>
<code bash>
tabul data head date_dim@sqlite
</code>
<console>
The first 10 rows of the data resource (date_dim@sqlite):
d_date_sk   d_date       d_date_id    d_month_seq   d_day_name   d_moy   d_year
---------   ----------   ----------   -----------   ----------   -----   ------
        1   2025-05-12   2025-05-12        202505   Monday           5     2025
        2   2025-05-11   2025-05-11        202505   Sunday           5     2025
        3   2025-05-10   2025-05-10        202505   Saturday         5     2025
        4   2025-05-09   2025-05-09        202505   Friday           5     2025
        5   2025-05-08   2025-05-08        202505   Thursday         5     2025
        6   2025-05-07   2025-05-07        202505   Wednesday        5     2025
        7   2025-05-06   2025-05-06        202505   Tuesday          5     2025
        8   2025-05-05   2025-05-05        202505   Monday           5     2025
        9   2025-05-04   2025-05-04        202505   Sunday           5     2025
       10   2025-05-03   2025-05-03        202505   Saturday         5     2025
</console>
</unit>