I am using ODBC from a Python program to save the content of a Pandas dataframe into a Fabric warehouse table. I am seeing 2 different behaviors depending on where the Python program is run.
From my Windows PC from a CMD terminal running in VS Code, the Python program is able to create the table in the Warehouse itself. The Python version is 3.9. I got the same behavior using "ODBC Driver 17 for SQL Server" or "ODBC Driver 18 for SQL Server"
If the same program runs from inside a Linux container in an Azure Kubernetes Service pod, the table is created in DataflowsStagingWarehouse instead. The Python and ODBC are 3.11 and 17 respectively.
In both cases, the connections are to the same warehouse and they are made the same way. The essence are below.
import sqlalchemy as sa
connection_string_out = "Driver={%s};Server=%s,1433;Database=%s;Encrypt=yes;" % (odbc_driver, sql_endpoint, database)
connect_kwargs ['attrs_before'] = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct}
connection_url = sa.engine.URL.create("mssql+pyodbc", query={"odbc_connect": connection_string_out})
output_odbc_engine = sa.create_engine(connection_url, connect_args=connect_kwargs)
df.to_sql(name=output_table, con=output_odbc_engine, index=False, if_exists=if_exists_mode, dtype=col_types)
I wasn't even aware of the existence of DataflowsStagingWarehouse. It was puzzling at first to see that the Python program ran successfully but the table was not listed in the warehouse. Only when I installed SQL Server Management Studio did I see the DataflowsStagingWarehouse and the table in it.
My workflow does not and cannot include using data pipeline to move the table from staging to the final destination.
I am trying to understand what needs to be changed so that the table is always created in the the warehouse itself and not in the staging area.
Thanks.
