Creating an external table from a Yandex Object Storage bucket table using a configuration file

To create an external table from a table in a Yandex Object Storage bucket, you need to provide a static access key for the service account in the query. You can do this using the S3 protocol and a configuration file stored on the HTTP server.

To create an external table using a configuration file:

1. Set up your web server with a configuration file.
2. Create an external table.

If you no longer need the resources you created, delete them.

Getting startedGetting started

Prepare the infrastructure:

Set up your web server with a configuration fileSet up your web server with a configuration file

Set up a web server on your virtual machine and create an s3 configuration file on the web server:

1. Connect to the virtual machine over SSH.

2. Create a folder named /opt/gp_http_server and put into it the s3.config file with the static key parameters and the other configuration settings for the s3 protocol:

   sudo mkdir /opt/gp_http_server
   

   s3.config file contents:

   [default]
   accessid = "<static_key_ID>"
   secret = "<static_key>"
   threadnum = 4
   chunksize = 67108864
   low_speed_limit = 10240
   low_speed_time = 60
   encryption = true
   version = 2
   proxy = ""
   autocompress = true
   verifycert = true
   server_side_encryption = ""
   # gpcheckcloud config
   gpcheckcloud_newline = "\n"
   

3. In the /etc/systemd/system/ folder, put a web server config file named gp_s3_config_http.service:

   [Unit]
   Description=HTTP server config to connect Greenplum with Object Storage
   After=network.target
   
   [Service]
   KillMode=process
   Restart=on-failure
   Type=simple
   User=root
   ExecStart=python3 -m http.server 8553 --directory /opt/gp_http_server
   
   [Install]
   WantedBy=multi-user.target
   

4. Start your web server:

   sudo systemctl daemon-reload && \
   sudo systemctl enable gp_s3_config_http && \
   sudo systemctl start gp_s3_config_http
   

5. To check the state of the web server, run the command below:

   sudo systemctl status gp_s3_config_http
   

Create an external tableCreate an external table

1. Connect to the Managed Service for Greenplum® cluster.

2. Run a query to create an external table referencing the example.csv table in the bucket:

   CREATE READABLE EXTERNAL TABLE s3_ext_table(id int, year int)
   LOCATION('s3://storage.yandexcloud.net/<bucket_name>/example.csv config_server=http://<VM_internal_IP_address>:8553/s3.config region=ru-central1-a')
   FORMAT 'csv';
   

   A successful query will return the following response:

   CREATE EXTERNAL TABLE
   

3. Query the external table for data:

   SELECT * FROM s3_ext_table;
   

   Expected result:

    id | year
   ----+------
    10 | 2010
   (1 row)
   

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.