Hi, I'm trying to write data from Alteryx to Snowflake using a Data Stream In In-Database tool. The operation is failing with error
"Error: Data Stream In (5): Error creating table "AYX230525d0e1564861d9e6d42902fa0d8cdbc67c": SQL access control error:
Insufficient privileges to operate on schema 'ALTERYX_DATA_SOURCES'
CREATE TABLE AYX230525d0e1564861d9e6d42902fa0d8cdbc67c (metric NUMBER(20,0),offering varchar(254))"
My database connection specifies a user and role that has USAGE permission granted on both the Snowflake database and schema. I have selected "Create Temporary Table" from the "Creation Mode" drop-down of the Data Stream In config.
My In-DB Connection is a Sytem DSN, which works as expected with a regular Output Data tool.
When I log into Snowflake with the user/password specified in the In-DB Connection, I can successfully run a CREATE TEMPORARY TABLE statement.
Given the error message above, do you think that the "Create Temporary Table" Creation Mode is actually trying to run CREATE TABLE instead?
If not, what should I change in my set-up to allow the successful creation of the Temporary Table as required by the Connection?
Or, is this use case (simply uploading data to a Snowflake table for later analysis - data volume in the millions of lines) better handled by the Output Data tool?
Thanks,
Tony
Create Table or Create Temporary Table will depend on the tool config:
Chris
Hi Chris, I've got Create Temporary Table selected as the Creation Mode. From the error message it looks like Alteryx is trying to run a CREATE TABLE.
I see what you mean in your output "CREATE TABLE". Maybe a question for the Alteryx support team?
I'm not sure what the USAGE permission is for the Snowflake database and schema. Not sure all of the permissions you may need.
I haven't written to a Temp table in Snowflake yet, only Oracle.
My log file only gives me one line for the Data Stream In tool "Info: Data Stream In (11): The Temporary Table "AYX230525cbadd83ab28ed6ab05888" has been created."