[Yandex Cloud documentation](../../index.md) > [Yandex MPP Analytics for PostgreSQL](../index.md) > [Tutorials](index.md) > Creating an external table from an Object Storage bucket table using a configuration file # Creating an external table from a Yandex Object Storage bucket table using a configuration file To [create an external table](../operations/pxf/create-table.md) from a table in a Yandex Object Storage bucket, you need to provide in the query a [static access key](../../iam/concepts/authorization/access-key.md) for the service account. You can do this using the [S3 protocol](https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/7/greenplum-database/admin_guide-external-g-s3-protocol.html) 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](#set-web-server). 1. [Create an external table](#create-ext-table). If you no longer need the resources you created, [delete them](#clear-out). ## Required paid resources {#paid-resources} * Yandex MPP Analytics for PostgreSQL cluster, which includes the use of computing resources allocated to hosts, storage and backup size (see [Yandex MPP Analytics for PostgreSQL pricing](../pricing/index.md)). * NAT gateway: hourly use of the gateway and its outgoing traffic (see [Yandex Virtual Private Cloud pricing](../../vpc/pricing.md)). * Object Storage bucket: use of storage, data operations (see [Object Storage pricing](../../storage/pricing.md)). * VM instance, which includes the use of computing resources, storage, public IP address, and OS (see [Yandex Compute Cloud pricing](../../compute/pricing.md)). * Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)). ## Getting started {#before-you-begin} Set up your infrastructure: {% list tabs group=instructions %} - Manually {#manual} 1. [Create a Greenplum® cluster](../operations/cluster-create.md) with any suitable configuration. 1. In the cluster subnet, [set up a NAT gateway](../../vpc/operations/create-nat-gateway.md) and [create a security group](../../vpc/operations/security-group-create.md) allowing all incoming and outgoing traffic from all addresses. 1. [Create a virtual machine on Linux](../../compute/operations/vm-create/create-linux-vm.md) in the same cloud network as the Greenplum® cluster. 1. [Create a Object Storage bucket](../../storage/operations/buckets/create.md) with restricted access. [Upload](../../storage/operations/objects/upload.md) the `example.csv` file with a test table to it: ```csv 10,2010 ``` 1. [Create a static access key](../../iam/operations/authentication/manage-access-keys.md#create-access-key) for the service account. - Terraform {#tf} 1. If you do not have Terraform yet, [install it](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform). 1. [Get the authentication credentials](../../tutorials/infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file. 1. [Configure and initialize a provider](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf). 1. Place the configuration file in a separate working directory and [specify the parameter values](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file. 1. In the working directory, create a file called `example.csv` with a test table: ```csv 10,2010 ``` 1. Download the [infrastructure configuration file](https://github.com/yandex-cloud-examples/yc-greenplum-config-server-for-s3/blob/main/greenplum-s3-vm.tf) to the same working directory. This file describes: * Network. * Subnet. * Service account with a static access key. * Greenplum® cluster in Yandex MPP Analytics for PostgreSQL. * Bucket to contain the `example.csv` file. * Virtual machine on [Ubuntu 20.04](https://yandex.cloud/en/marketplace/products/yc/ubuntu-20-04-lts). 1. In `greenplum-s3-vm.tf`, specify the following: * Password for `user` for access to the Greenplum® cluster. * Virtual machine image ID. * Username and path to the SSH key for virtual machine access. * Folder ID for the same service account as specified in the provider settings. * Bucket name that must be unique throughout Object Storage. 1. In the terminal, navigate to the directory containing the infrastructure plan. 1. To make sure the configuration files are correct, run this command: ```bash terraform validate ``` Terraform will display any configuration errors detected in your files. 1. Create the infrastructure required to follow the steps in this tutorial: 1. Run this command to view the planned changes: ```bash terraform plan ``` If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources. 1. If everything looks correct, apply the changes: 1. Run this command: ```bash terraform apply ``` 1. Confirm updating the resources. 1. Wait for the operation to complete. All the required resources will be created in the specified folder. You can check resource availability and their settings in the [management console](https://console.yandex.cloud). 1. To retrieve the static key properties, run this command in the working directory: ```bash terraform output -raw access_key > static-key.txt && \ echo \n >> static-key.txt && \ terraform output -raw secret_key >> static-key.txt ``` The command saves to a file named `static-key.txt` the static key ID and the static key you are going to need later. 1. Go to the [management console](https://console.yandex.cloud) and [set up a NAT gateway](../../vpc/operations/create-nat-gateway.md) for the subnet hosting your cluster. {% endlist %} ## Set up your web server with a configuration file {#set-web-server} Set up a web server on your virtual machine and create an `s3` configuration file on the web server: 1. [Connect](../../compute/operations/vm-connect/ssh.md) to the VM over SSH. 1. Create a folder named `/opt/gp_http_server` and put into it the `s3.config` file with the static key properties and other configuration settings for the `s3` protocol: ```bash sudo mkdir /opt/gp_http_server ``` `s3.config` file contents: ```config [default] accessid = "" secret = "" 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" ``` 1. In the `/etc/systemd/system/` folder, put a web server config file named `gp_s3_config_http.service`: ```config [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 ``` 1. Start your web server: ```bash sudo systemctl daemon-reload && \ sudo systemctl enable gp_s3_config_http && \ sudo systemctl start gp_s3_config_http ``` 1. To check your web server's state, run this command: ```bash sudo systemctl status gp_s3_config_http ``` ## Create an external table {#create-ext-table} 1. [Connect to the Greenplum® cluster](../operations/connect/index.md). 1. Run a query to create an external table referencing the `example.csv` table in the bucket: ```sql CREATE READABLE EXTERNAL TABLE s3_ext_table(id int, year int) LOCATION('s3://storage.yandexcloud.net//example.csv config_server=http://:8553/s3.config region=ru-central1-a') FORMAT 'csv'; ``` A successful query will return the following response: ```sql CREATE EXTERNAL TABLE ``` 1. Query the external table for data: ```sql SELECT * FROM s3_ext_table; ``` Expected result: ```text id | year ----+------ 10 | 2010 (1 row) ``` ## Delete the resources you created {#clear-out} Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them: {% list tabs group=instructions %} - Manually {#manual} 1. [Delete the VM](../../compute/operations/vm-control/vm-delete.md). 1. If you reserved a public static IP address for your virtual machine, [delete it](../../vpc/operations/address-delete.md). 1. [Delete the bucket in Object Storage](../../storage/operations/buckets/delete.md). 1. [Delete the Greenplum® cluster](../operations/cluster-delete.md). 1. [Delete the service account](../../iam/operations/sa/delete.md). 1. [Delete the subnet](../../vpc/operations/subnet-delete.md). 1. [Delete the route table](../../vpc/operations/delete-route-table.md). 1. [Delete the NAT gateway](../../vpc/operations/delete-nat-gateway.md). 1. [Delete the cloud network](../../vpc/operations/network-delete.md). - Terraform {#tf} 1. In the terminal window, go to the directory containing the infrastructure plan. {% note warning %} Make sure the directory has no Terraform manifests with the resources you want to keep. Terraform deletes all resources that were created using the manifests in the current directory. {% endnote %} 1. Delete resources: 1. Run this command: ```bash terraform destroy ``` 1. Confirm deleting the resources and wait for the operation to complete. All the resources described in the Terraform manifests will be deleted. {% endlist %} _Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries._