MicrosoftHi Linda_Wang
When I was doing a copy from ADF to Snowflake using a pipeline (with a single table), here is the query that ADF is firing to Snowflake
COPY INTO "STAGING"."MYTABLENAME" ( "HOSPITALKEY", "HOSPITALCD", "HOSPITALNM", "CCN", "TAXID", "NPI1", "NPI2", "NPI3", "NPI4", "BEGINDT", "ENDDT", "INSERTDTS", "UPDATEDTS", "CREATEDBY", "UPDATEDBY", "SUBREGIONKEY") FROM (SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16 FROM @stg) FORCE = TRUE LOAD_UNCERTAIN_FILES = TRUE -- it is including all 16 columns in the query
For the same table, if I was doing a bulk copy, along with other tables, here is the query that ADF is firing to snowflake
COPY INTO "STAGING"."MYTABLENAME" ( "CCN", "NPI1", "NPI2", "NPI3", "NPI4") FROM (SELECT $4, $6, $7, $8, $9 FROM @stg) FORCE = TRUE LOAD_UNCERTAIN_FILES = TRUE -- not including all columns
And failing with below error
{ "errorCode": "2200", "message": "ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22000] NULL result in a non-nullable column\n File 'b44982d5-1536-4bc6-af71-67151b9b4bdb/SnowflakeImportCopyCommand/data_b44982d5-1536-4bc6-af71-67151b9b4bdb_ffe04232-c156-4621-b18b-ebf9376dd281.txt', line 2, character 1\n Row 1, column \"MYTABLENAME\"[\"HOSPITALKEY\":1]\n If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22000] NULL result in a non-nullable column\n File 'b44982d5-1536-4bc6-af71-67151b9b4bdb/SnowflakeImportCopyCommand/data_b44982d5-1536-4bc6-af71-67151b9b4bdb_ffe04232-c156-4621-b18b-ebf9376dd281.txt', line 2, character 1\n Row 1, column \"MYTABLENAME\"[\"HOSPITALKEY\":1]\n If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.,Source=Snowflake,'", "failureType": "UserError", "target": "CopyTablestoCDW", "details": [] }
It is obvious that, query fired by ADF does not include HOSPITALKEY, which is a primary key in the target table and because of that it is failing.
Does anyone faced the same scenario? i am not sure why ADF is changing queries when doing a single table copy and a bulk table copy. Is there an option that I can set, so that it won’t change queries between single table copy and bulk table copy?
Thank you
Ramakrishna Tumati
