SQL in SQL out

SQL in SQL out

Have a staging area and a production environment? why not...

• Materialize views in production from views in Staging with a Query.
• Limit your data in production by JOINing down to current term

embulk-input-jdbc

query: |
  SELECT submission_dim.*
  FROM submission_dim 
    JOIN assignment_dim ON (assignment_dim.id = submission_dim.assignment_id)
    JOIN course_dim ON (course_dim.id = assignment_dim.course_id)
    JOIN enrollment_term_dim ON (enrollment_term_dim.id = course_dim.enrollment_term_id)
  WHERE enrollment_term_dim.canvas_id IN (5518,5517,5516,5515,5514,5513)
  ORDER BY submission_dim.id ASC

in:
  type: sqlserver
  driver_path: {{ env.EMBULK_MSSQL_DRIVER }}
  native_driver: {{ env.EMBULK_NATIVE_DRIVER }}
  host: {{ env.EMBULK_MSSQLH }}
  port: {{ env.EMBULK_MSSQLHP }}
  user: {{ env.EMBULK_MSSQLU }}
  password: {{ env.EMBULK_MSSQLP }}
  database: {{ env.EMBULK_MSSQLDB }}
  default_timezone: {{ env.EMBULK_TZ }}
  query: |
    SELECT * FROM dbo.enrollment_master_vw WHERE canvas_term_id >= 5513
out:
  type: sqlserver
  driver_path: {{ env.EMBULK_MSSQL_DRIVER }}
  native_driver: {{ env.EMBULK_NATIVE_DRIVER }}
  host: {{ env.EMBULK_PROD_MSSQLH }}
  port: {{ env.EMBULK_PROD_MSSQLHP }}
  user: {{ env.EMBULK_PROD_MSSQLU }}
  password: {{ env.EMBULK_PROD_MSSQLP }}
  database: {{ env.EMBULK_MSSQLDB }}
  table: sy_enrollment_master
  mode: replace
  insert_method: native
  column_options:
    global_canvas_id: {type: 'BIGINT NOT NULL', value_type: long}
    canvas_user_id: {type: 'INT NULL', value_type: int}
    sis_user_id: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    user_login: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    sortable_name: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    enrollment_type: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    enrollment_status: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    created_at: {value_type: string, timestamp_format: '%Y-%m-%d %H:%M:%S.%L'} 
    updated_at: {value_type: string, timestamp_format: '%Y-%m-%d %H:%M:%S.%L'} 
    last_activity_at: {value_type: string, timestamp_format: '%Y-%m-%d %H:%M:%S.%L'} 
    current_login_at: {value_type: string, timestamp_format: '%Y-%m-%d %H:%M:%S.%L'} 
    last_login_at: {value_type: string, timestamp_format: '%Y-%m-%d %H:%M:%S.%L'} 
    course_id: {type: 'BIGINT NOT NULL', value_type: long}
    canvas_course_id: {type: 'INT NULL', value_type: int}
    course_sis_id: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    course_name: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    course_section_id: {type: 'BIGINT NOT NULL', value_type: long}
    canvas_course_section_id: {type: 'INT NULL', value_type: int}
    default_section: {type: 'VARCHAR(256) NULL', value_type: string}
    enrollment_term_id: {type: 'BIGINT NOT NULL', value_type: long}
    canvas_term_id: {type: 'INT NULL', value_type: int}
    account_id: {type: 'BIGINT NOT NULL', value_type: long}
    canvas_account_id: {type: 'INT NULL', value_type: int}
    account_sis_id: {type: 'VARCHAR(256) NULL', value_type: string}
    department_id: {type: 'BIGINT NOT NULL', value_type: long}
    department_name: {type: 'NVARCHAR(256) NULL', value_type: nstring}
    sub_account_id: {type: 'VARCHAR(256) NULL', value_type: string}
    sub_account_name: {type: 'NVARCHAR(256) NULL', value_type: nstring}