how to create a table if not exists ?

[djouallah]

I want sqlmesh to create the table first before starting inserting data, is this the right way ?

MODEL (
  name pricetoday,
  kind INCREMENTAL_UNMANAGED,
  cron '*/5 * * * *',
);


CREATE TABLE IF NOT EXISTS @{this_model} (
  REGIONID VARCHAR,
  RUNNO DOUBLE,
  DISPATCHINTERVAL DOUBLE,
  INTERVENTION DOUBLE,
  RRP DOUBLE,
  EEP DOUBLE,
  ROP DOUBLE,
  APCFLAG DOUBLE,
  MARKETSUSPENDEDFLAG DOUBLE,
  RAISE6SECRRP DOUBLE,
  RAISE6SECROP DOUBLE,
  RAISE6SECAPCFLAG DOUBLE,
  RAISE60SECRRP DOUBLE,
  RAISE60SECROP DOUBLE,
  RAISE60SECAPCFLAG DOUBLE,
  RAISE5MINRRP DOUBLE,
  RAISE5MINROP DOUBLE,
  RAISE5MINAPCFLAG DOUBLE,
  RAISEREGRRP DOUBLE,
  RAISEREGROP DOUBLE,
  RAISEREGAPCFLAG DOUBLE,
  LOWER6SECRRP DOUBLE,
  LOWER6SECROP DOUBLE,
  LOWER6SECAPCFLAG DOUBLE,
  LOWER60SECRRP DOUBLE,
  LOWER60SECROP DOUBLE,
  LOWER60SECAPCFLAG DOUBLE,
  LOWER5MINRRP DOUBLE,
  LOWER5MINROP DOUBLE,
  LOWER5MINAPCFLAG DOUBLE,
  LOWERREGRRP DOUBLE,
  LOWERREGROP DOUBLE,
  LOWERREGAPCFLAG DOUBLE,
  PRE_AP_ENERGY_PRICE DOUBLE,
  PRE_AP_RAISE6_PRICE DOUBLE,
  PRE_AP_RAISE60_PRICE DOUBLE,
  PRE_AP_RAISE5MIN_PRICE DOUBLE,
  PRE_AP_RAISEREG_PRICE DOUBLE,
  PRE_AP_LOWER6_PRICE DOUBLE,
  PRE_AP_LOWER60_PRICE DOUBLE,
  PRE_AP_LOWER5MIN_PRICE DOUBLE,
  PRE_AP_LOWERREG_PRICE DOUBLE,
  RAISE1SECRRP DOUBLE,
  RAISE1SECROP DOUBLE,
  RAISE1SECAPCFLAG DOUBLE,
  LOWER1SECRRP DOUBLE,
  LOWER1SECROP DOUBLE,
  LOWER1SECAPCFLAG DOUBLE,
  PRE_AP_RAISE1_PRICE DOUBLE,
  PRE_AP_LOWER1_PRICE DOUBLE,
  CUMUL_PRE_AP_ENERGY_PRICE DOUBLE,
  CUMUL_PRE_AP_RAISE6_PRICE DOUBLE,
  CUMUL_PRE_AP_RAISE60_PRICE DOUBLE,
  CUMUL_PRE_AP_RAISE5MIN_PRICE DOUBLE,
  CUMUL_PRE_AP_RAISEREG_PRICE DOUBLE,
  CUMUL_PRE_AP_LOWER6_PRICE DOUBLE,
  CUMUL_PRE_AP_LOWER60_PRICE DOUBLE,
  CUMUL_PRE_AP_LOWER5MIN_PRICE DOUBLE,
  CUMUL_PRE_AP_LOWERREG_PRICE DOUBLE,
  CUMUL_PRE_AP_RAISE1_PRICE DOUBLE,
  CUMUL_PRE_AP_LOWER1_PRICE DOUBLE,
  "week" DOUBLE,
  SETTLEMENTDATE TIMESTAMP WITH TIME ZONE,
  date DATE,
  file VARCHAR,
  "YEAR" BIGINT
);


SET VARIABLE list_of_files_price_today =
(
  WITH xxxx AS (
    SELECT
      concat('abfss://udf@onelake.dfs.fabric.microsoft.com/data.Lakehouse/Files/', extracted_filepath) AS file
    FROM 'abfss://udf@onelake.dfs.fabric.microsoft.com/data.Lakehouse/Files/Reports/Current/DispatchIS_Reports/download_log.csv'
    WHERE parse_filename(extracted_filepath) NOT IN (SELECT DISTINCT file FROM @{this_model})
    ORDER BY file
    LIMIT 5000
  )
  SELECT list(file) FROM xxxx
);

WITH RAW_price AS (
    FROM read_csv(getvariable('list_of_files_price_today'),
      Skip = 1,
      header = 0,
      all_varchar = 1,
      columns = {
        'I' : 'VARCHAR',
        'DISPATCH' : 'VARCHAR',
        'PRICE' : 'VARCHAR',
        'xx' : 'VARCHAR',
        'SETTLEMENTDATE' : 'VARCHAR',
        'RUNNO' : 'VARCHAR',
        'REGIONID' : 'VARCHAR',
        'DISPATCHINTERVAL' : 'VARCHAR',
        'INTERVENTION' : 'VARCHAR',
        'RRP' : 'VARCHAR',
        'EEP' : 'VARCHAR',
        'ROP' : 'VARCHAR',
        'APCFLAG' : 'VARCHAR',
        'MARKETSUSPENDEDFLAG' : 'VARCHAR',
        'LASTCHANGED' : 'VARCHAR',
        'RAISE6SECRRP' : 'VARCHAR',
        'RAISE6SECROP' : 'VARCHAR',
        'RAISE6SECAPCFLAG' : 'VARCHAR',
        'RAISE60SECRRP' : 'VARCHAR',
        'RAISE60SECROP' : 'VARCHAR',
        'RAISE60SECAPCFLAG' : 'VARCHAR',
        'RAISE5MINRRP' : 'VARCHAR',
        'RAISE5MINROP' : 'VARCHAR',
        'RAISE5MINAPCFLAG' : 'VARCHAR',
        'RAISEREGRRP' : 'VARCHAR',
        'RAISEREGROP' : 'VARCHAR',
        'RAISEREGAPCFLAG' : 'VARCHAR',
        'LOWER6SECRRP' : 'VARCHAR',
        'LOWER6SECROP' : 'VARCHAR',
        'LOWER6SECAPCFLAG' : 'VARCHAR',
        'LOWER60SECRRP' : 'VARCHAR',
        'LOWER60SECROP' : 'VARCHAR',
        'LOWER60SECAPCFLAG' : 'VARCHAR',
        'LOWER5MINRRP' : 'VARCHAR',
        'LOWER5MINROP' : 'VARCHAR',
        'LOWER5MINAPCFLAG' : 'VARCHAR',
        'LOWERREGRRP' : 'VARCHAR',
        'LOWERREGROP' : 'VARCHAR',
        'LOWERREGAPCFLAG' : 'VARCHAR',
        'PRICE_STATUS' : 'VARCHAR',
        'PRE_AP_ENERGY_PRICE' : 'VARCHAR',
        'PRE_AP_RAISE6_PRICE' : 'VARCHAR',
        'PRE_AP_RAISE60_PRICE' : 'VARCHAR',
        'PRE_AP_RAISE5MIN_PRICE' : 'VARCHAR',
        'PRE_AP_RAISEREG_PRICE' : 'VARCHAR',
        'PRE_AP_LOWER6_PRICE' : 'VARCHAR',
        'PRE_AP_LOWER60_PRICE' : 'VARCHAR',
        'PRE_AP_LOWER5MIN_PRICE' : 'VARCHAR',
        'PRE_AP_LOWERREG_PRICE' : 'VARCHAR',
        'RAISE1SECRRP' : 'VARCHAR',
        'RAISE1SECROP' : 'VARCHAR',
        'RAISE1SECAPCFLAG' : 'VARCHAR',
        'LOWER1SECRRP' : 'VARCHAR',
        'LOWER1SECROP' : 'VARCHAR',
        'LOWER1SECAPCFLAG' : 'VARCHAR',
        'PRE_AP_RAISE1_PRICE' : 'VARCHAR',
        'PRE_AP_LOWER1_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_ENERGY_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_RAISE6_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_RAISE60_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_RAISE5MIN_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_RAISEREG_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_LOWER6_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_LOWER60_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_LOWER5MIN_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_LOWERREG_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_RAISE1_PRICE' : 'VARCHAR',
        'CUMUL_PRE_AP_LOWER1_PRICE' : 'VARCHAR',
        'OCD_STATUS' : 'VARCHAR',
        'MII_STATUS' : 'VARCHAR',
      },
      filename = 1,
      null_padding = true,
      ignore_errors = 1,
      auto_detect = false
    )
    WHERE I = 'D'
      AND PRICE = 'PRICE'
  )
  SELECT
    REGIONID,
    CAST(columns(* EXCLUDE (SETTLEMENTDATE, REGIONID, I,xx, PRICE, filename, OCD_STATUS, MII_STATUS, DISPATCH, PRICE_STATUS, LASTCHANGED)) AS DOUBLE), -- Cast remaining columns to DOUBLE
    CAST(SETTLEMENTDATE AS TIMESTAMPTZ) AS SETTLEMENTDATE,
    CAST(SETTLEMENTDATE AS date) AS date,
    parse_filename(filename) AS file,
    year(CAST(SETTLEMENTDATE AS TIMESTAMPTZ)) AS YEAR
  FROM RAW_price
  ORDER BY date

[erindru]

SQLMesh already creates the tables automatically, your model just needs to define the data to load.

Please run through the quickstart to get an idea of how SQLMesh works.

[djouallah]

@erindru I am doing self reference basically to load data based on a filename, I can't find a way to tell sqlmesh, if it is the first run then create an empty table and load everything, otherwise, load only the the delta , my workaround was to use CREATE TABLE IF NOT EXISTS @{this_model} but it does not works

[erindru]

It sounds like you are thinking in terms of dbt.

SQLMesh as at right at this moment always creates first and then loads, rather than create+load in the same operation. SQLMesh will detect self referential queries and still create an empty table with the desired structure although you may need to specify columns manually if it cant infer them.

You may be interested in #5189 which is about to change SQLMesh to create+load in the same step which more closely mimics dbt.

In this case though you still wouldn't specify the CREATE TABLE manually as part of your model. Instead, in your final SELECT... FROM RAW_price query, you'd list out all the columns (and cast them to the correct types if SQLMesh cant infer them).

SQLMesh will use this information to create the table automatically.

Please reach out on Slack with usage questions rather than our issue tracker