Is there any way to wrap the `snowflake resource` in an sqla dagster #announcements

Is there any way to wrap the `snowflake_resource` ...

Basil V

11/11/2020, 11:39 PM

Is there any way to wrap the

snowflake_resource

in an sqlalchemy connection? Or would you guys be open to a PR to make the

snowflake_resource

use sqlalchemy? The reason is, some pandas function such as

.to_sql

require an sqlalchemy connection, so as far as I can tell / and via my own testing the pandas SQL utilities won't be available for Snowflake using the Dagster

snowflake_resource

directly as it is now. Any thoughts?

dwall

11/11/2020, 11:41 PM

Not sure if you've seen this already, but the snowflake python package

pandas_tools

module also has a builtin

write_pandas()

method for doing this: https://docs.snowflake.com/en/user-guide/python-connector-api.html#label-python-connector-api-write-pandas

dwall

11/11/2020, 11:41 PM

we ultimately decided not to use it because IIRC it requires writing intermediate data temporarily to disk, but it is another option

dwall

11/11/2020, 11:46 PM

for writing DataFrames to Snowflake, the pattern we ultimately landed on is writing the DataFrame to S3 using a

df_to_s3

solid that writes the DataFrame as a file to S3 and outputs the S3 object location, and then passes that S3 location to a downstream

s3_to_snowflake

solid that dynamically builds the

COPY INTO

statement based on the S3 object location:

dwall

11/11/2020, 11:46 PM

we landed on this way because we noticed it was significantly more performant than the

write_pandas

and

.to_sql()

routes

Basil V

11/11/2020, 11:55 PM

Yeah that all makes sense and I was aware of the

write_pandas

function. The reason I've been wanting to use

.to_sql

from my dataframe is because it will create the table for me if it doesn't exist already, with the proper schema. I'm migrating a postgres DB to snowflake and have 50+ tables to copy over, so I don't want to have to manually create each table, define types etc. Any other workaround you can think for this issue?

Basil V

11/11/2020, 11:55 PM

write_pandas

won't create the table for me

dwall

11/12/2020, 12:09 AM

ah I see I see. Yeah, not sure of another route that would create the tables for you. The only thing I could think of is to create a solid that iterates through all postgres tables, inspects the schema, and dynamically creates and runs an equivalent

CREATE TABLE

snowflake command before copying data

sandy

11/12/2020, 1:21 AM

@Basil V I've actually faced this issue myself. If you wanted to submit a PR, I'd be happy to take a look

Basil V

11/12/2020, 8:45 PM

@sandy just put up a PR https://github.com/dagster-io/dagster/pull/3214 — I tried to maintain all the default behavior, very open to suggestions for improving it. Lmk what you guys think. Thanks!

Basil V

01/08/2021, 8:31 PM

Hey @sandy Happy New Year to you guys! I just got around to responding to your PR comments and updating accordingly (for PR #3214 link above). Thanks for reviewing—let me know if you need me to do anything else on it in order to get it merged. Much appreciated!

sandy

01/09/2021, 12:53 AM

Thanks @Basil V! I'll take another look

💯 1

Basil V

01/13/2021, 5:26 PM

Hey @sandy I went through your second review comments, updated the formatting etc. It looks like it is still failing 3 tests—but not sure if those are due to my PR or something else. Anyways let me know if it looks good or if you need me to make any other changes. Thanks again!

Basil V

01/18/2021, 8:44 PM

Hey @sandy saw that you merged my PR thanks again for all the help reviewing! Do you know when the next release is expected / when can I anticipate that these changes will be available? Thanks!

sandy

01/19/2021, 4:15 PM

Hey Basil - our next release should be on Thursday this week

Basil V

01/19/2021, 4:17 PM

Awesome thanks!

32 Views

Open in Slack

Previous Next