Search code examples
snowflake-cloud-data-platformtpc

Query works fine in browser but fails in CLI

I have the TPC-H benchmark installed. I can run this command in the browser and it works fine:

select * from h_customer;

But if I run it from the Linux CLI, it simply never returns.

If I modify it to be smaller, it will work in the CLI:

select * from h_customer limit 10;

That returns just fine. I can then continue:

select * from h_customer limit 100; -- This works.
select * from h_customer limit 1000; -- This hangs.

The History view shows that the server got the query with the 'limit 1000', and processed it correctly. It was run in a reasonable time and the history view shows the proper 1,000 rows.

I can reproduce this with other tables and queries. It seems to be a volume of data issue on the client side. But returning 1,000 rows should be trivial. And I would expect it to return much more.

Solution

  • Snowflake sends small result sets directly to the client. For larger ones, it directs the client to the result set cache to download one or more compressed files. The result set cache is coming from the cloud provider, not directly from snowflakecomputing.com.

    1,000 rows is one threshold to switch between the two, and I've seen very wide tables do the same with fewer rows. The client then tries to download an S3 file, Azure blob, or GCS file depending on where Snowflake is running. Something is blocking your Linux client from downloading those files.

    Usually it's a firewall with stateful packet inspection such as from Palo Alto or Cisco. They sometimes decrypt HTTPS traffic and drop packets that look like they should be "snowflakecomputing.com" but are coming from AWS, Azure, or GCP.

    It could also be a proxy server. Since it's working from the browser, something is different between how the two are picking up large result sets. The browser may have a proxy configured. You can set a proxy for all Snowflake clients including SnowSQL https://docs.snowflake.com/en/user-guide/snowsql-start.html#using-a-proxy-server.

    Check with your networking team to have see if something's dropping packets from the cloud provider or snowflakecomputing.com.