transform.malloynb

>>>markdown
# Transforming Data

Malloy can be used to for data transformation.  Files with the extension <code>.malloysql</code> are interpreted as a combination of both Malloy and SQL.  Each language is blocked by a preceeding `>>>`.  Each block can do anything that is appropriate in the language.  In Malloy, for example, you can import other Malloy files.  In SQL, you can execute any DDL command.

In the example below, we create a simple semantic model for the table `airports`.  There are two SQL sections, one creates a CSV file of the major airports, the other creates a view in the database with a list of states, the airport count in each state, and the count of each of the facility types for each state.

This mechanism can be used to create governed datasets for use in other tooling: the transformation code for these tables can be governed with source control, and the queries take advantage of Malloy's reusibility.

File <code>airports.malloysql</code>:
  ```malloysql
  >>>malloy
  source: airports is duckdb.table('data/airports.parquet') extend {
    measure: airport_count is count()

    view: major_airports is {
      where: major = 'Y'
      select: *
    }

    view: by_state is {
      group_by: state
      aggregate: airport_count
      nest: by_fac_type is  {
        group_by: fac_type
        aggregate: airport_count
      }
    }
  }

  >>>sql connection:duckdb

  -- create a table using a Malloy query

  COPY (
  %{
    airports -> major_airports
  }%
  ) TO 'major_airports.csv' (HEADER) 

  >>>sql

  -- create a view using a Malloy query

  CREATE OR REPLACE VIEW  by_state as (
  %{
    airports -> by_state
  }%
  ) 
  ```