Support for changing databases#124
Support for changing databases#124semcha wants to merge 2 commits intodbt-msft:masterfrom semcha:master
Conversation
|
@swanderz @mikaelene Hi! How I can test this PR in azuresql? |
|
@semcha I'm not sure! CircleCI should be testing this automatically... |
|
@semcha you should re-open this! This link will show you how the integration tests are failing on Azure SQL, you just have to sign into Circle Ci with your GItHub account. It's also quite easy to make a basic Azure SQL database on a free Azure subscription and use that to run the integration tests against it... issueerror messageSQL query responsible for messageselect
table_catalog as [database],
table_name as [name],
table_schema as [schema],
case
when table_type = 'BASE TABLE' then 'table'
when table_type = 'VIEW' then 'view'
else table_type
end as [type]
from **********************.information_schema.tables
where table_schema = 'dbt_test_azure_sql_210330220928759444776398'root causelooks like the bug right now might have to do with our current quoting policy because our database (starrred out) right now for security reasons, has a few from "**********************"."information_schema"."tables" |
There was a problem hiding this comment.
@semcha you're closer than anyone's ever gotten before! if you accept my changes about properly quoting, the new error message is
Reference to database and/or server name in
[****************].sys.schemasis not supported in this version of SQL Server.
So here's two potential approaches:
- try a
USE [{{relation.database}}]at the top of these macros? - implement some tricky conditional logic based on the result of the
SERVERPROPERTY()function. there's a previous discussion of this option in microsoft/dbt-synapse#18 (relevant comment).
@mikaelene what do you think? we're close I feel!
| {% call statement('create_schema') -%} | ||
| use [{{ relation.database }}]; | ||
| if not exists ( | ||
| select * | ||
| from sys.schemas | ||
| where name = '{{ relation.without_identifier().schema }}' | ||
| ) | ||
| begin | ||
| execute('create schema {{ relation.without_identifier().schema }}') | ||
| end | ||
| {% endcall %} |
There was a problem hiding this comment.
I like the formating you've done here, but we should probably have formatting be a separate PR, so we can more easily see the diffs required to enable using multiple databases
| if exists ( | ||
| select * | ||
| from sys.indexes | ||
| where name = '{{ cci_name }}' | ||
| and [object_id] = object_id('{{ relation.include(database=False) }}') |
There was a problem hiding this comment.
same thing about formatting from other note
| begin | ||
| drop index {{cci_name}} on {{ relation.include(database=False) }} | ||
| end | ||
| create clustered columnstore index {{ cci_name }} | ||
| on {{ relation.include(database=False) }} |
There was a problem hiding this comment.
are you adding a begin and end here? can you help me understand why?
| when table_type = 'VIEW' then 'view' | ||
| else table_type | ||
| end as [type] | ||
| from {{ schema_relation.database }}.information_schema.tables |
There was a problem hiding this comment.
gotta put db name into square brackets to properly escape weird chars like - in the db name.
| from {{ schema_relation.database }}.information_schema.tables | |
| from [{{ schema_relation.database }}].information_schema.tables |
| {{ return(load_result('list_schemas').table) }} | ||
| {% call statement('list_schemas', fetch_result=True, auto_begin=False) -%} | ||
| select name as [schema] | ||
| from {{ database }}.sys.schemas |
There was a problem hiding this comment.
gotta put db name into square brackets to properly escape weird chars like - in the db name.
| from {{ database }}.sys.schemas | |
| from [{{ database }}].sys.schemas |
| numeric_precision, | ||
| numeric_scale | ||
| from INFORMATION_SCHEMA.COLUMNS | ||
| from {{ relation.database }}.INFORMATION_SCHEMA.COLUMNS |
There was a problem hiding this comment.
gotta put db name into square brackets to properly escape weird chars like - in the db name.
| from {{ relation.database }}.INFORMATION_SCHEMA.COLUMNS | |
| from [{{ relation.database }}].INFORMATION_SCHEMA.COLUMNS |
| #### fixes | ||
| - solved a bug in snapshots introduced in v0.19.0. Fixes: [#108](https://github.com/dbt-msft/dbt-sqlserver/issues/108), [#117](https://github.com/dbt-msft/dbt-sqlserver/issues/117). | ||
|
|
||
| - changing databases is now supported. [#110](https://github.com/dbt-msft/dbt-sqlserver/issues/110) |
There was a problem hiding this comment.
| - changing databases is now supported. [#110](https://github.com/dbt-msft/dbt-sqlserver/issues/110) | |
| - changing databases is now supported. [#110](https://github.com/dbt-msft/dbt-sqlserver/issues/110) thanks [@semcha ](https://github.com/semcha) |
Fixes #110.