How to read data users.csv.gz?

Question

Hi, I am referring link https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/quickstart-distribute-tables

And getting this error:

citus=> SELECT * FROM create_extension('azure_storage');

create_extension

---

(1 row)

citus=> COPY github_users FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz';

ERROR: azure_storage: failed to read from service

citus=>

Answer 1

To read data from users.csv.gz, you can use the COPY command in PostgreSQL. Here's an example command to load the data into a table:

COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz'
WITH (FORMAT 'csv');

This command will load the data from the specified URL into the github_users table. The FORMAT parameter specifies the file format, which in this case is CSV.

If you're encountering an error when running the COPY command, it's possible that the azure_storage extension is not properly installed or configured. Make sure that you have followed the instructions in the context to create and configure the extension and map the storage account.

---

References:

• How to ingest data using pg_azure_storage in Azure Cosmos DB for PostgreSQL
• Create and distribute tables in Azure Cosmos DB for PostgreSQL

Answer 2

Hi @Santosh Anand,

Thanks for the question and using MS Q&A platform.

• It looks like you're encountering an error while trying to use the COPY command with the azure_storage extension in Citus.
• The error message suggests that there's a problem reading from the Azure Blob Storage. Please refer to this document: How to ingest data using pg_azure_storage in Azure Cosmos DB for PostgreSQL.
• The COPY command is used to move data between files and database tables. COPY is a server-based command that requires access to the disk, usually limited to server administrators. However, Azure Cosmos DB for PostgreSQL operates as a Platform-as-a-Service (PaaS) solution, which means that users aren't granted superuser privileges. COPY command is thus not fully supported on the platform.
• Alternatively, \COPY is a command available in psql and other client interfaces that facilitates direct interaction with the local file system of the machine where it is executed.

For more Additional Information on COPY command, please refer to this document: COPY command on Azure Cosmos DB for PostgreSQL.

Hope this helps. Do let us know if you any further queries. f this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.