Can't insert external table data into table in synapse dedicated sql pool

Question

In Synapse dedicated pool, I'm trying to create an external table, and a dedicated table, and then insert the external table into the dedicated table but I keep getting the following error:

Explicit conversion from data type bigint to date is not allowed.

These are my create and insert statements below:

External table

CREATE EXTERNAL TABLE [gold].[ExternalTable]
(
 [Column1] [DATE] NULL
,[Column2] [DATE] NULL
,[Column3] [DATE] NULL
,[Column4] [DATETIME2] NULL
,[Column5] [DATETIME2] NULL
,[Column6] [SMALLINT] NULL
,[Column7] [BIGINT] NULL
,[Column8] [BIGINT] NULL
,[Column9] [BIGINT] NULL
,[Column10] [VARBINARY] (8000) NULL
,[Column11] [VARCHAR] (8000) NULL
,[Column12] [VARCHAR] (8000) NULL
,[Column13] [VARCHAR] (8000) NULL
,[Column14] [VARCHAR] (8000) NULL
,[Column15] [VARCHAR] (8000) NULL
,[Column16] [VARCHAR] (8000) NULL
,[Column17] [VARCHAR] (8000) NULL
,[Column18] [VARCHAR] (8000) NULL
,[Column19] [VARCHAR] (8000) NULL
,[Column20] [VARCHAR] (8000) NULL
,[Column21] [VARCHAR] (8000) NULL
,[Column22] [VARCHAR] (8000) NULL
,[Column23] [VARCHAR] (8000) NULL
,[Column24] [VARCHAR] (8000) NULL
,[Column25] [VARCHAR] (8000) NULL
,[Column26] [VARCHAR] (8000) NULL
,[Column27] [VARCHAR] (8000) NULL
,[Column28] [VARCHAR] (8000) NULL
,[Column29] [VARCHAR] (8000) NULL
)
WITH (DATA_SOURCE = [gold_dbx], LOCATION = N'/Path/To/Parquet/', FILE_FORMAT = [ParquetFormat], REJECT_TYPE = VALUE, REJECT_VALUE = 0 );

Dedicated table

CREATE TABLE [gold].[Table]
      (
 [Column1] [DATE] NULL
,[Column2] [DATE] NULL
,[Column3] [DATE] NULL
,[Column4] [DATETIME2] NULL
,[Column5] [DATETIME2] NULL
,[Column6] [SMALLINT] NULL
,[Column7] [BIGINT] NULL
,[Column8] [BIGINT] NULL
,[Column9] [BIGINT] NULL
,[Column10] [VARBINARY] (8000) NULL
,[Column11] [VARCHAR] (8000) NULL
,[Column12] [VARCHAR] (8000) NULL
,[Column13] [VARCHAR] (8000) NULL
,[Column14] [VARCHAR] (8000) NULL
,[Column15] [VARCHAR] (8000) NULL
,[Column16] [VARCHAR] (8000) NULL
,[Column17] [VARCHAR] (8000) NULL
,[Column18] [VARCHAR] (8000) NULL
,[Column19] [VARCHAR] (8000) NULL
,[Column20] [VARCHAR] (8000) NULL
,[Column21] [VARCHAR] (8000) NULL
,[Column22] [VARCHAR] (8000) NULL
,[Column23] [VARCHAR] (8000) NULL
,[Column24] [VARCHAR] (8000) NULL
,[Column25] [VARCHAR] (8000) NULL
,[Column26] [VARCHAR] (8000) NULL
,[Column27] [VARCHAR] (8000) NULL
,[Column28] [VARCHAR] (8000) NULL
,[Column29] [VARCHAR] (8000) NULL
)
)WITH
                 (
                            distribution = HASH(DataHash),
                            CLUSTERED columnstore INDEX
               )  


Insert INTO [gold].[Table]
SELECT * FROM [gold].ExternalTable]

Edit: I'm noticing that if I try to just SELECT the external table I'm getting the same issue. I'm guessing the problem is between converting the parquet file into an external table. Anyone experienced this before?

Answer 1

Issue was in the order of the columns. I was creating a parquet file from a delta table in DBX and then creating an external table from this parquet file. To fix this I explicitly called out the columns when creating the parquet file instead of doing a SELECT * FROM

Answer 2

The error message mentions an issue with converting a BIGINT data type to DATE.

• Verify that the data types in the external table match the corresponding data types in your dedicated table.

I have tried an example for external and dedicated table and inserted data.

Exteranal table creation:

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat') 
    CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] 
    WITH ( FORMAT_TYPE = PARQUET)
GO
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'folder02_dileepsynapsegen2_dfs_core_windows_net') 
    CREATE EXTERNAL DATA SOURCE [folder02_dileepsynapsegen2_dfs_core_windows_net] 
    WITH (
        LOCATION = 'abfss://[email protected]' 
    )
GO
CREATE EXTERNAL TABLE external_tb02 (
    [Column1] DATE,
    [Column2] DATE,
    [Column3] DATE,
    [Column4] DATETIME2,
    [Column5] DATETIME2,
    [Column6] SMALLINT,
    [Column7] bigint,
    [Column8] bigint,
    [Column9] bigint,
    [Column10] varbinary(8000),
    [Column11] varchar(4000),
    [Column12] varchar(4000),
    [Column13] varchar(4000),
    [Column14] varchar(4000),
    [Column15] varchar(4000),
    [Column16] varchar(4000),
    [Column17] varchar(4000),
    [Column18] varchar(4000),
    [Column19] varchar(4000),
    [Column20] varchar(4000),
    [Column21] varchar(4000),
    [Column22] varchar(4000),
    [Column23] varchar(4000),
    [Column24] varchar(4000),
    [Column25] varchar(4000),
    [Column26] varchar(4000),
    [Column27] varchar(4000),
    [Column28] varchar(4000),
    [Column29] varchar(4000)
)
WITH (
    LOCATION = 'output_data.parquet',
    DATA_SOURCE = folder02_dileepsynapsegen2_dfs_core_windows_net,
    FILE_FORMAT = SynapseParquetFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0
);




SELECT* FROM external_tb02

Output:

Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10    Column11    Column12    Column13    Column14    Column15    Column16    Column17    Column18    Column19    Column20    Column21    Column22    Column23    Column24    Column25    Column26    Column27    Column28    Column29
2023-11-01T00:00:00.0000000 2023-11-02T00:00:00.0000000 2023-11-03T00:00:00.0000000 2023-11-04T12:00:00.0000000 2023-11-05T12:00:00.0000000 1   100 200 300 0x736F6D655F64617461    value11 value12 value13 value14 value15 value16 value17 value18 value19 value20 value21 value22 value23 value24 value25 value26 value27 value28 value29

Creation of the dedicated table:

CREATE TABLE ded_tbl_02 (
    [Column1] DATE,
    [Column2] DATE,
    [Column3] DATE,
    [Column4] DATETIME2,
    [Column5] DATETIME2,
    [Column6] SMALLINT,
    [Column7] BIGINT,
    [Column8] BIGINT,
    [Column9] BIGINT,
    [Column10] VARBINARY(8000),
    [Column11] VARCHAR(4000),
    [Column12] VARCHAR(4000),
    [Column13] VARCHAR(4000),
    [Column14] VARCHAR(4000),
    [Column15] VARCHAR(4000),
    [Column16] VARCHAR(4000),
    [Column17] VARCHAR(4000),
    [Column18] VARCHAR(4000),
    [Column19] VARCHAR(4000),
    [Column20] VARCHAR(4000),
    [Column21] VARCHAR(4000),
    [Column22] VARCHAR(4000),
    [Column23] VARCHAR(4000),
    [Column24] VARCHAR(4000),
    [Column25] VARCHAR(4000),
    [Column26] VARCHAR(4000),
    [Column27] VARCHAR(4000),
    [Column28] VARCHAR(4000),
    [Column29] VARCHAR(4000)
) WITH
(
    DISTRIBUTION = HASH(Column7),
    CLUSTERED COLUMNSTORE INDEX
);

Insert INTO ded_tbl_02
SELECT * FROM external_tb02

Output:

Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10    Column11    Column12    Column13    Column14    Column15    Column16    Column17    Column18    Column19    Column20    Column21    Column22    Column23    Column24    Column25    Column26    Column27    Column28    Column29
2023-11-01T00:00:00.0000000 2023-11-02T00:00:00.0000000 2023-11-03T00:00:00.0000000 2023-11-04T12:00:00.0000000 2023-11-05T12:00:00.0000000 1   100 200 300 0x736F6D655F64617461    value11 value12 value13 value14 value15 value16 value17 value18 value19 value20 value21 value22 value23 value24 value25 value26 value27 value28 value29