Hello,
I'm facing issues loading data from Fabric Lakehouse Table/File to Fabric Warehouse. Below is my notebook code. Im using pyspark
lakehouse_table_path = f"{lakehouse_path}/Tables/{table_name}"
lakehouse_df = spark.read.format("delta").load(lakehouse_table_path)
# Infer the schema
schema = lakehouse_df.schema
# Function to map Spark SQL data types to SQL Server data types
def map_data_type(spark_type):
if spark_type == "string":
return "VARCHAR(4000)"
elif spark_type == "INT":
return "INT"
elif spark_type == "bigint":
return "BIGINT"
elif spark_type == "smallint":
return "INT"
elif spark_type == "Double":
return "FLOAT"
elif spark_type == "boolean":
return "BIT"
elif spark_type == "timestamp":
return "DATETIME2(3)"
else:
return spark_type # Default to NVARCHAR for other types
# Generate the CREATE TABLE statement
create_table_query = f"CREATE TABLE {DestTableSchemaName}.{DestTableName} ("
for field in schema.fields:
sql_type = map_data_type(field.dataType.simpleString())
create_table_query += f"{field.name} {sql_type}, "
create_table_query = create_table_query.rstrip(", ") + ")"
print(create_table_query)
df= pd.read_parquet(ParquetFileLocation)
constr = (
f"Driver={{ODBC Driver 18 for SQL Server}};"
f"Server={server};"
f"Database={database};"
f"UID={clientId};"
f"PWD={clientSecret};"
f"Authentication=ActiveDirectoryServicePrincipal;"
f"Encrypt=yes;"
f"TrustServerCertificate=no;"
f"Connection Timeout=30;"
)
# Establish connection
conn = pyodbc.connect(constr)
cursor = conn.cursor()
print(create_table_query)
cursor.execute(create_table_query)
conn.commit()
tblnm ="dbo.contract"
print(tblnm)
engine = create_engine(f"mssql+pyodbc:///?odbc_connect={constr}")
df.to_sql(tblnm, engine, if_exists='append', index=False)
cursor.close()
conn.close()
Now when i run the script, based on the schema i got the table created in the Fabric Warehouse.
however, when i try to push the dataframe data into the created table, i get the below error.
ProgrammingError<span>: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The data type 'datetime' is not supported in this edition of SQL Server.
(24574) (SQLExecDirectW)") [SQL:
CREATE TABLE dbo.contract ( [Id] BIGINT NULL, [ContractId] BIGINT NULL, [VIN] VARCHAR(max) NULL, [RatedVIN] VARCHAR(max) NULL, [Make] VARCHAR(max) NULL, [Model] VARCHAR(max) NULL, [ModelYear] SMALLINT NULL, [Class] VARCHAR(max) NULL, [SeriesTrim] VARCHAR(max) NULL,
[FuelType] VARCHAR(max) NULL, [Cylinders] VARCHAR(max) NULL, [InServiceDate] DATETIME NULL, [IsNew] BIT NULL,
[PurchasePrice] VARCHAR(max) NULL, [LastModifiedBy] VARCHAR(max) NULL,[CreatedOnDateUtc] DATETIME NULL )
however, the table i created has the correct data types.
Not sure why when i try to push the dataframe data, it tries to create the same table again with unsupported datatypes
