Enable create or replace sql syntax #125

Fokko · 2020-11-18T16:29:48Z

With Delta we can do an atomic operation to replace the current version of the table, with a new version using the create or replace syntax.

For delta, and only delta, we can use the CREATE OR REPLACE syntax to atomically replace the table. This will make sure that the table is still available all the time.

resolves #124

Description

Checklist

I have signed the CLA
I have run this code in development and it appears to resolve the stated issue
This PR includes tests, or tests are not required/relevant for this PR
I have updated the CHANGELOG.md and added information about my change to the "dbt next" section.

Ran the tests:

MacBook-Pro-van-Fokko:dbt-integration-tests fokkodriesprong$ ./bin/run-with-profile default
Test direct copying of source tables    # features/001_basic_materializations.feature
  Test materialized='view' -- @1.1   ..........
  Test materialized='table' -- @1.2   ..........
  Test materialized='incremental' -- @1.3   ..........

Test re-materializing models as different types    # features/002_rematerialize_models.feature
  Materialize as view (view) first, then view (view) -- @1.1   ............
  Materialize as view (view) first, then table (parquet) -- @1.2   ............
  Materialize as view (view) first, then incremental (parquet) -- @1.3   ............
  Materialize as table (parquet) first, then view (view) -- @1.4   ............
  Materialize as table (delta) first, then view (view) -- @1.5   ............
  Materialize as table (parquet) first, then table (parquet) -- @1.6   ............
  Materialize as table (parquet) first, then table (delta) -- @1.7   ............
  Materialize as table (delta) first, then table (parquet) -- @1.8   ............
  Materialize as table (delta) first, then table (delta) -- @1.9   ............
  Materialize as table (parquet) first, then incremental (parquet) -- @1.10   ............
  Materialize as table (parquet) first, then incremental (parquet) -- @1.11   ............
  Materialize as incremental (parquet) first, then view (view) -- @1.12   ............
  Materialize as incremental (parquet) first, then table (parquet) -- @1.13   ............
  Materialize as incremental (parquet) first, then incremental (parquet) -- @1.14   ............

2 features passed, 0 failed, 0 skipped
17 scenarios passed, 0 failed, 0 skipped
198 steps passed, 0 failed, 0 skipped, 0 undefined
Took 17m21.438s

With Delta we can do an atomic operation to replace the current version of the table, with a new version using the create or replace syntax.

jtcohen6 · 2020-11-18T16:35:03Z

Going to figure out why Circle didn't run tests against this PR

jtcohen6 · 2020-11-20T22:17:07Z

Hey @Fokko, I think the issue here is that the CI jobs ran against the fork in the "Fokko" Circle organization as opposed to the "Fishtown Analytics" ones (which have the appropriate env vars for sandbox Databricks connection). I believe this is fixable if you go into the CircleCI Project Settings for the "Fokko" organization, and on the Overview tab, click Stop Building, such that CircleCI should no longer follow Fokko / dbt.

into fd-enable-create-or-replace

Fokko · 2020-12-04T12:39:41Z

@jtcohen6 Sorry for the late reply, was kinda busy. I couldn't find the page that you're referring to. I've unfollowed the project in CircleCI, let's see if that fixes anything.

jtcohen6

@Fokko This looks great! Thanks for getting the CI working.

If you're up to do it, I'd support swapping out the existing {% if config.file_format == 'delta' %} checks in incremental + snapshot to use relation.is_delta instead. That doesn't need to happen in this PR, though.

dbt/adapters/spark/relation.py

dbt/include/spark/macros/materializations/snapshot.sql

Co-authored-by: Jeremy Cohen <[email protected]>

Fokko · 2020-12-23T20:44:49Z

@jtcohen6 I've reverted the incremental.sql as well. The .is_delta only works for existing relations :)

jtcohen6

Fair enough, it was a nice thought :) I'm quite happy with this!

@Fokko could you add a changelog entry (and add yourself to the list of contributors)? I'd love to get this in before v0.19.0.

The failing test seems to be an unrelated issue with set statements + the new SQL Analytics endpoints. I'll open a separate PR to disable the incremental step for that test suite.

into fd-enable-create-or-replace

Fokko · 2020-12-31T12:22:19Z

@jtcohen6 thanks! I've added myself to the CHANGELOG. And!

Enable create or replace sql syntax

d99ed62

With Delta we can do an atomic operation to replace the current version of the table, with a new version using the create or replace syntax.

cla-bot bot added the cla:yes label Nov 18, 2020

Fokko mentioned this pull request Nov 18, 2020

Test for the Delta file format as well dbt-labs/dbt-integration-tests#8

Closed

Trim the whitespace

880db89

Fokko force-pushed the fd-enable-create-or-replace branch from dac74ac to 880db89 Compare November 18, 2020 19:01

Fix the test to create or replace

b5b6936

Fokko force-pushed the fd-enable-create-or-replace branch from b89349a to b5b6936 Compare November 18, 2020 19:08

Make flake8 happy

8cb6be5

Merge branch 'master' of https://github.com/fishtown-analytics/dbt-spark

8161b80

into fd-enable-create-or-replace

jtcohen6 approved these changes Dec 4, 2020

View reviewed changes

dbt/adapters/spark/relation.py Show resolved Hide resolved

Use is_delta instead of file_format == 'delta'

8794c89

jtcohen6 reviewed Dec 14, 2020

View reviewed changes

dbt/include/spark/macros/materializations/snapshot.sql Outdated Show resolved Hide resolved

Fokko and others added 3 commits December 23, 2020 20:09

Update dbt/include/spark/macros/materializations/snapshot.sql

a9478eb

Co-authored-by: Jeremy Cohen <[email protected]>

Clean up error messages when doing snapshots

589afed

Revert incremental for now

1f8febd

jtcohen6 approved these changes Dec 31, 2020

View reviewed changes

Fokko added 2 commits December 31, 2020 13:17

Add me to the contributors list

3ebb870

Merge branch 'master' of https://github.com/fishtown-analytics/dbt-spark

1614094

into fd-enable-create-or-replace

jtcohen6 merged commit 0b6b922 into dbt-labs:master Dec 31, 2020

Fokko deleted the fd-enable-create-or-replace branch December 31, 2020 17:02

This was referenced Jan 11, 2021

SQL Endpoint only supports merge incremental strategy [and still doesn't yet] #138

Closed

Rationalize file formats + incremental strategies #140

Closed

gumartinm mentioned this pull request Apr 29, 2021

support schema evolution for delta lake #162

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Enable create or replace sql syntax #125

Enable create or replace sql syntax #125

Uh oh!

Fokko commented Nov 18, 2020 •

edited

Loading

Uh oh!

jtcohen6 commented Nov 18, 2020

Uh oh!

jtcohen6 commented Nov 20, 2020 •

edited

Loading

Uh oh!

Fokko commented Dec 4, 2020 •

edited

Loading

Uh oh!

jtcohen6 left a comment

Uh oh!

Uh oh!

Uh oh!

Fokko commented Dec 23, 2020

Uh oh!

jtcohen6 left a comment

Uh oh!

Fokko commented Dec 31, 2020

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Enable create or replace sql syntax #125

Enable create or replace sql syntax #125

Uh oh!

Conversation

Fokko commented Nov 18, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Description

Checklist

Uh oh!

jtcohen6 commented Nov 18, 2020

Uh oh!

jtcohen6 commented Nov 20, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Fokko commented Dec 4, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

jtcohen6 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Fokko commented Dec 23, 2020

Uh oh!

jtcohen6 left a comment

Choose a reason for hiding this comment

Uh oh!

Fokko commented Dec 31, 2020

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Fokko commented Nov 18, 2020 •

edited

Loading

jtcohen6 commented Nov 20, 2020 •

edited

Loading

Fokko commented Dec 4, 2020 •

edited

Loading