Azure Extension failing to authenticate/update DuckDB to latest

[J-Harmson]

Hello,

I am new to SQL-Flow and am trying to use it to add Kafka messages to Azure Data Lake Storage Gen2. I believe to do this I need to use the DuckDB Azure Extension: https://duckdb.org/docs/stable/extensions/azure

I am having an issue connecting to my ADLS instance, when trying to use:

commands:
name: load extensions
sql: |
INSTALL azure;
LOAD azure;
SET azure_storage_connection_string = '{{ SQLFLOW_AZURE_CONNECTION_STRING }}';

...

COPY sqlflow_sink_batch
TO 'abfss://[container]@[account name].dfs.core.windows.net/[path]'
(FORMAT 'parquet', PARTITION_BY (SourceDatePartition), OVERWRITE_OR_IGNORE 1);

the connection string I am using is in this format: DefaultEndpointsProtocol=https;AccountName=[account name];AccountKey=[key];EndpointSuffix=core.windows.net

I get a DuckDB error stating it cannot create the directory. I have tried opening my test ADLS instance up to rule out any networking issues and it still can't get through, meaning it must be a permissions issue. When further investigating, it looks like the version of DuckDB is out of date (0.9.2, latest is 1.2). In 0.9.2, the Azure Extension was in experimental: https://duckdb.org/docs/0.9/extensions/azure with more authentication features available in 0.9.3.

Am I doing something wrong? and also can the version of DuckDB also be updated to a more recent version?

[J-Harmson]

Also spotted that the error message changes to whatever the 'TO abfss://...' value is, which is making it sound like its not actually using the environment variables I have set at all, and/or not running the commands block at the top.

[turbolytics]

Thank you for giving SQLFlow a try @J-Harmson and creating an issue! We'll get a DuckDB version upgrade released this weekend. I'll make sure to walk through the errors you're seeing by the end of the weekend to get some resolution. Sorry for the friction with this, but I appreciate you trying SQLFlow out!

I'll walk through the commands initialization and see where additional debug / info can be added to give you better feedback as well.

[J-Harmson]

@turbolytics thank you very much! I can give you the full config if that's any help, I'm attempting to run this with docker-compose with other tools like Kafka but can get rid of those. The pipeline.yaml file is mounted as a volume to the sqlflow container. The env variables are defined in the container but have also just tried hard-coding them.

I think having Duck DB updated will solve the auth issue as it opens more options for authentication and allows use of the defaultAzureCredentialChain.

[turbolytics]

@J-Harmson the config would be very helpful! Thank you

[J-Harmson]

Here is part of my Docker Compose:

sqlflow:
image: my-sql-flow:latest
depends_on:
wait-for-kafka-topic:
condition: service_completed_successfully
environment:
SQLFLOW_KAFKA_BROKERS: kafka:9092
SQLFLOW_AZURE_STORAGE_CONNECTION_STRING: DefaultEndpointsProtocol=https;AccountName=[account name];AccountKey=[key];
SQLFLOW_AZURE_ACCOUNT_NAME: [account name]
SQLFLOW_AZURE_ACCOUNT_KEY: [key]
volumes:
- ./sqlflow/pipeline.yaml:/app/pipeline.yaml
command: ["run", "/app/pipeline.yaml"]
networks:
- kafka-network

And here is the pipeline.yaml with the way I am expecting it to work with Duck DB version 1.2.x: https://duckdb.org/docs/stable/extensions/azure.html:

commands:
  name: load extensions
    sql: |
      INSTALL azure;
      LOAD azure;
  name: create adls secret
    sql: |
      CREATE SECRET adls_conn (
        TYPE azure,
        PROVIDER credential_chain,
        CHAIN 'default;cli;env',
        ACCOUNT_NAME {{ SQLFLOW_AZURE_ACCOUNT_NAME }},
      );

pipeline:
  name: kafka-azure-duckdb-sink
  description: "Writes Kafka stream into Azure Data Lake using DuckDB Azure extension"
  batch_size: 50

  source:
    type: kafka
    kafka:
      brokers: [{{ SQLFLOW_KAFKA_BROKERS|default('localhost:9092') }}]
      group_id: sql-flow-consumer-001
      auto_offset_reset: earliest
      topics:
        - "[topic]"

  handler:
    type: 'handlers.InferredMemBatch'
    sql: |
      SELECT
        *,
        CAST(TO_TIMESTAMP(payload.source.ts_ms / 1000) AS DATE) AS SourceDatePartition
      FROM batch

  sink:
    type: sqlcommand
    sqlcommand:
      substitutions:
          - var: $sqlflow_uuid
            type: uuid4
      sql: |
        COPY sqlflow_sink_batch
        TO 'abfss://[container]@[account].dfs.core.windows.net/[path]/$sqlflow_uuid.parquet'
        (FORMAT 'parquet', PARTITION_BY (SourceDatePartition), OVERWRITE_OR_IGNORE 1);

I also have a Dockerfile which downloads the Azure extension and some other stuff but with the Duck DB update I may not need that anymore.

Hope this is useful! Apologies if the formatting is a bit off. Thanks in advance

[turbolytics]

Hello @J-Harmson

Just want to resay, Thank you for giving SQLFlow a try! I think you're the second person to reach out with the interest of trying SQLFlow, and I really appreciate it. I'm debugging the pipeline locally and notice a couple of things:

Pipeline Configuration File Structure

I'm not sure if it's a copy paste error, but the first thing I notice is that the commands entry needs a list/array of commands.

Instead of:

commands:
  name: load extensions
    sql: |
      INSTALL azure;
      LOAD azure;
  name: create adls secret
    sql: |
      CREATE SECRET adls_conn (
        TYPE azure,
        PROVIDER credential_chain,
        CHAIN 'default;cli;env',
        ACCOUNT_NAME {{ SQLFLOW_AZURE_ACCOUNT_NAME }},
      );

it should be:

commands:
  - name: load extensions
    sql: |
      INSTALL azure;
      LOAD azure;
  - name: create adls secret
    sql: |
      CREATE SECRET adls_conn (
        TYPE azure,
        PROVIDER credential_chain,
        CHAIN 'default;cli;env',
        ACCOUNT_NAME {{ SQLFLOW_AZURE_ACCOUNT_NAME }},
      );

I think you're the first person to engage with using SQLFlow - So i know it's missing feedback to make detecting these issues easy!

There is a lightweight validate command available, but the output I think needs improvement to be less cryptic:

python cmd/sql-flow.py config validate path/to/your/azure-debug-pipeline.yml

Unquoted ACCOUNT_NAME

The following has unquoted account name:

commands:
  - name: load extensions
    sql: |
      INSTALL azure;
      LOAD azure;
  - name: create adls secret
    sql: |
      CREATE SECRET adls_conn (
        TYPE azure,
        PROVIDER credential_chain,
        CHAIN 'default;cli;env',
        ACCOUNT_NAME {{ SQLFLOW_AZURE_ACCOUNT_NAME }},
      );

I believe it should be quoted:

commands:
  - name: load extensions
    sql: |
      INSTALL azure;
      LOAD azure;
  - name: create adls secret
    sql: |
      CREATE SECRET adls_conn (
        TYPE azure,
        PROVIDER credential_chain,
        CHAIN 'default;cli;env',
        ACCOUNT_NAME '{{ SQLFLOW_AZURE_ACCOUNT_NAME }}',
      );

How to connect to azure?

Dislaimer: I don't really have any experience in azure. How should authentication work? Right now https://duckdb.org/docs/stable/extensions/azure.html#credential_chain-provider CREDENTIAL_CHAIN is configured. Does your docker image have access to your credential chain?

Can you use CONFIG provider here with the connection string instead? https://duckdb.org/docs/stable/extensions/azure.html#config-provider. At least to test to see if we can get SQLFlow sinking to azure?

commands:
  - name: load extensions
    sql: |
      INSTALL azure;
      LOAD azure;
  - name: create adls secret
    sql: |
      CREATE SECRET adls_conn (
        TYPE azure,
         CONNECTION_STRING '{{ SQLFLOW_AZURE_STORAGE_CONNECTION_STRING }}'
      );

Use the SQLFlow Debug Endpoint to query the duckdb db that SQLFlow uses

This endpoint start the SQLFlow pipeline with an exposed HTTP server. The server is useful for querying the DuckDB database that backs your SQLFlow pipeline. We need to publish a tutorial on how to use this, but this shows it in action. The following commands will:

• Start the pipeline against an empty kafka topic, with the HTTP Debug endpoint enabled
• Have the pipeline idle waiting for messages
• Make a curl request to the SQLFlow pipline using the FROM duckdb_secrets(); query

#122

• Start SQLFlow with HTTP SQL Debug Enabled

python cmd/sql-flow.py run dev/config/examples/azure-debug.yml --with-http-debug
2025-04-13 15:00:42,662 [INFO] executing command load extensions
 * Serving Flask app 'sql-flow'
 * Debug mode: on
2025-04-13 15:00:42,696 [INFO] executing command create adls secret
2025-04-13 15:00:42,718 [INFO] consumer loop starting
2025-04-13 15:00:42,720 [INFO] WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on http://127.0.0.1:5000
2025-04-13 15:00:42,720 [INFO] Press CTRL+C to quit

• Make a curl request to debug endpoint to list secrets

% curl 'localhost:5000/debug?sql=FROM%20duckdb_secrets%28%29%3B'
[
  [
    "adls_conn",
    "azure",
    "credential_chain",
    false,
    "memory",
    [
      "azure://",
      "az://",
      "abfss://",
      "abfs://"
    ],
    "name=adls_conn;type=azure;provider=credential_chain;serializable=true;scope=azure://,az://,abfss://,abfs://;account_name=;chain=default;cli;env"
  ]
]

The debug endpoint sql= param needs to be URL encoded. The command that is executed in this example is:

FROM duckdb_secrets();

https://duckdb.org/docs/stable/configuration/secrets_manager.html#listing-secrets

---

Questions

• What feedback would you like to see to make this process easier/smoother/better?

[J-Harmson]

Hi @turbolytics I see that you have updated Duck DB so thanks for that, but I believe to use the image it needs to be pushed to a new release? (I've never contributed to OSS so could be wrong). The CREATE SECRET stuff is in the newest version of Duck DB, not 0.9.2, in which it seems the only way to authenticate with Azure is using:

SET azure_storage_connection_string = '<your_connection_string>';

Which I have tried but still get the authentication error: https://duckdb.org/docs/0.9/extensions/azure

commands:
  - name: load extensions
    sql: |
      INSTALL azure;
      LOAD azure;
  - name: set azure storage connection string
    sql: |
      SET azure_storage_connection_string = '{{ SQLFLOW_AZURE_CONNECTION_STRING }}';

My credential chain should be able to access the ADLS instance, but I can try connection string using the CONFIG provider first (but again, creating secrets for this extension is in 0.10 +

The commands part you mentioned was a copy-paste error, but I did have the connection string unquoted so thanks for spotting that! Still getting the same error though :(

[J-Harmson]

Also in terms of improvements, I would say the first easy one you could make is to rename the tool (which I've seen you have a ticket open for anyway) as there are a couple of SQL-Flow tools already that come up in searches before this one. A couple others could be some improved are the logging as you've already stated and down the line it could be really good to have Python support as well as SQL, perhaps being able to utilising pandas dataframes and structured streaming to be able to manipulate/join streams in flight like you can with SQL, but I appreciate this is a new tool so just an idea for the future!

[turbolytics]

@J-Harmson I forgot to post that duckdb 1.2.2 is available in the following images:

• turbolytics/sql-flow:70b2d0c
• turbolytics/sql-flow:latest

docker run -it --entrypoint=python3 turbolytics/sql-flow:70b2d0c -c "import duckdb;print(duckdb.__version__)"

1.2.2

[J-Harmson]

Ah great, thanks for specifying! I have now gotten past the authentication stage, however, I am now hitting another error stating:
duckdb.duckdb.NotImplementedException: Not implemented Error: AzureDfsStorageFileSystem: FileExists is not implemented!

I think this is an issue with the Azure Extension in Duck DB rather than SQL-Flow and there's a couple of open tickets for it: duckdb/duckdb-iceberg#66 and duckdb/duckdb-azure#50

So might be a little stuck for now :(

Thanks for all your help so far though!