[Yandex Cloud documentation](../../index.md) > [Yandex MetaData Hub](../index.md) > Apache Hive™ Metastore > Tutorials > Shared use of Yandex Data Processing tables through Apache Hive™ Metastore

# Shared use of Yandex Data Processing tables through Apache Hive™ Metastore

You can save data from a [Yandex Data Processing cluster](../../data-proc/concepts/index.md) to a [Yandex Object Storage bucket](../../storage/concepts/bucket.md) using a separate [Apache Hive™ Metastore cluster](../concepts/metastore.md) to store table metadata. Thus you can access the saved data from a different Yandex Data Processing cluster that has access to the bucket and is connected to the same Apache Hive™ Metastore cluster.

To set up shared use of tables by two Yandex Data Processing clusters through Apache Hive™ Metastore:

1. [Connect Yandex Data Processing to Apache Hive™ Metastore](#connect).
1. [Create a test table](#create-table).
1. [Get data in the second cluster](#test-target).

If you no longer need the resources you created, [delete them](#clear-out).

If a Yandex Data Processing cluster contains tables that should be available in another Yandex Data Processing cluster, [transfer the tables](../../data-proc/tutorials/metastore-import.md) to the appropriate cluster using Apache Hive™ Metastore.

{% note warning %}

If you want to configure an [access policy](../../storage/concepts/policy.md) for a bucket and connect to it from a Apache Hive™ Metastore cluster, you will need some additional infrastructure setup. For more information, see [this guide](../operations/metastore/s3-policy-connect.md).

{% endnote %}

## Required paid resources {#paid-resources}

* Yandex Data Processing cluster: use of computing resources with a Yandex Data Processing markup, use of network drives, retrieval and storage of logs, amount of outgoing traffic (see [Yandex Data Processing pricing](../../data-proc/pricing.md)).
* Apache Hive™ Metastore cluster: computing resources of cluster components (see [Yandex MetaData Hub pricing](../pricing.md)).
* Object Storage buckets: use of storage, data operations (see [Object Storage pricing](../../storage/pricing.md)).
* NAT gateway: hourly use of the gateway and its outgoing traffic (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 service account](../../iam/operations/sa/create.md) named `dataproc-s3-sa` and assign the `dataproc.agent` and `dataproc.provisioner` roles to it.
    1. In Object Storage, [create buckets](../../storage/operations/buckets/create.md) and [configure access](../../storage/operations/buckets/edit-acl.md) to them:
       
       1. Create a bucket for the input data and grant the `READ` permission for this bucket to the cluster service account.
       1. Create a bucket for the processing output and grant the cluster service account `READ and WRITE` permissions for this bucket.
    1. [Create a cloud network](../../vpc/operations/network-create.md) named `dataproc-network`.
    1. In `dataproc-network`, [create a subnet](../../vpc/operations/subnet-create.md) in any availability zone.
    1. [Set up a NAT gateway](../../vpc/operations/create-nat-gateway.md) for the subnet you created.
    1. [Create two Yandex Data Processing clusters](../../data-proc/operations/cluster-create.md) named `dataproc-source` and `dataproc-target` in any [suitable host configuration](../../data-proc/concepts/instance-types.md) with the following settings:

        * **Environment**: `PRODUCTION`.
        * **Services**:
            * `SPARK`
            * `YARN`
        * **Service account**: `dataproc-sa`.
        * **Properties**: `spark:spark.sql.hive.metastore.sharedPrefixes` with the `com.amazonaws,ru.yandex.cloud` value. It is required for PySpark jobs and integration with Apache Hive™ Metastore.
        * **Bucket name**: Bucket you created for output data.
        * **Network**: `dataproc-network`.

    1. If the cloud network uses [security groups](../../vpc/concepts/security-groups.md), [add](../../vpc/operations/security-group-add-rule.md) the following rule for outgoing traffic to the Yandex Data Processing cluster security group:

        * **Port range**: `9083`.
        * **Protocol**: `Any`.
        * **Source**: `CIDR`.
        * **CIDR blocks**: `0.0.0.0/0`.

- 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. Download the [dataproc-to-dataproc.tf](https://github.com/yandex-cloud-examples/yc-data-proc-to-data-proc/blob/main/dataproc-to-dataproc.tf) configuration file to the same working directory.

        This file describes:

        * [Network](../../vpc/concepts/network.md#network).
        * [Subnet](../../vpc/concepts/network.md#subnet).
        * [NAT gateway](../../vpc/concepts/gateways.md) and route table required for Yandex Data Processing.
        * [Security groups](../../vpc/concepts/security-groups.md) for the Yandex Data Processing clusters.
        * Service account for the Yandex Data Processing cluster.
        * Service account required to create buckets in Object Storage.
        * Buckets for input and output data.
        * Two Yandex Data Processing clusters.

    1. In `dataproc-to-dataproc.tf`, specify the following:

        * `folder_id`: Cloud folder ID, same as in the provider settings.
        * `input-bucket`: Input data bucket name.
        * `output-bucket`: Output data bucket name.
        * `dp_ssh_key`: Absolute path to the public key for the Yandex Data Processing clusters. Learn more about connecting to a Yandex Data Processing host over SSH [here](../../data-proc/operations/connect-ssh.md).

    1. Validate your Terraform configuration files using this command:

        ```bash
        terraform validate
        ```

        Terraform will display any configuration errors detected in your files.

    1. Create the required infrastructure:

        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).

{% endlist %}

## Connect Yandex Data Processing to Apache Hive™ Metastore {#connect}

1. [Create a Apache Hive™ Metastore cluster](../operations/metastore/cluster-create.md) version 3.1 in `dataproc-network`.

1. [Add](../../data-proc/operations/cluster-update.md) the `spark:spark.hive.metastore.uris` property with the `thrift://<Apache Hive™ Metastore_cluster_IP_address>:9083` value to the Yandex Data Processing cluster settings.

   To find out the Apache Hive™ Metastore cluster IP address, open the [management console](https://console.yandex.cloud), then go to **Yandex MetaData Hub**, and select the ![image](../../_assets/console-icons/database.svg) **Metastore** page in the left-hand panel. Copy the **IP address** column value for the cluster in question.

## Create a test table {#create-table}

In the `dataproc-source` cluster, create a test table named `countries` and upload it to Object Storage:

{% list tabs %}

- Spark

    1. Prepare a script file:

        1. Create a local file named `create-table.py` and paste the following script to it:

            {% cut "create-table.py" %}

            ```python
            from pyspark.sql.types import *
            from pyspark.sql import SparkSession

            # Creating a Spark session
            spark = SparkSession.builder \
                .appName("create-table") \
                .enableHiveSupport() \
                .getOrCreate()

            # Creating a data schema
            schema = StructType([StructField('Name', StringType(), True),
            StructField('Capital', StringType(), True),
            StructField('Area', IntegerType(), True),
            StructField('Population', IntegerType(), True)])

            # Creating a dataframe
            df = spark.createDataFrame([('Australia', 'Canberra', 7686850, 19731984), ('Austria', 'Vienna', 83855, 7700000)], schema)

            # Writing the dataframe to a bucket as a countries table
            df.write.mode("overwrite").option("path","s3a://<output_bucket_name>/countries").saveAsTable("countries")
            ```

            {% endcut %}

        1. In the script, specify the name of the output bucket where the file with the `countries` table will be saved.

        1. In the input bucket, create a folder named `scripts` and [upload](../../storage/operations/objects/upload.md#simple) the `create-table.py` file to it.

    1. [Create a PySpark job](../../data-proc/operations/jobs-pyspark.md#create) by specifying the path to the script file in the **Main python file** field: `s3a://<input_bucket_name>/scripts/create-table.py`.

    1. Wait for the job to complete and make sure the output bucket's `countries` folder contains the `part-00000-...` file.

{% endlist %}

The data from the created table is now stored in the Object Storage bucket and the table metadata is stored in the Apache Hive™ Metastore cluster. Now you can [delete](../../data-proc/operations/cluster-delete.md) the `dataproc-source` cluster.

## Get data in the second cluster {#test-target}

Upload the `countries` table metadata to the `dataproc-target` cluster and make sure the table is accessible in the cluster for further operations:

{% list tabs %}

- Spark

    1. Prepare a script file:

        1. Create a local file named `obtain-table.py` and paste the following script to it:

            {% cut "obtain-table.py" %}

            ```python
            from pyspark.sql import SparkSession

            # Creating a Spark session
            spark = SparkSession.builder \
                .appName("obtain-table") \
                .enableHiveSupport() \
                .getOrCreate()

            spark.catalog.listDatabases()

            # Getting the `countries` table metadata from Apache Hive™ Metastore
            df = spark.sql("describe extended countries")

            # Requesting data from the countries table
            df = spark.sql("select * from countries")

            # Testing table-to-bucket transfer
            df.repartition(1).write.csv(path='s3a://<output_bucket_name>/csv', header=True, sep=',')
            ```

            {% endcut %}

        1. In the script, specify the name of the output bucket where the CSV file with the `countries` table will be saved.
        1. [Upload](../../storage/operations/objects/upload.md#simple) the `obtain-table.py` file to the input data bucket’s `scripts` folder.

    1. [Create a PySpark job](../../data-proc/operations/jobs-pyspark.md#create) by specifying the path to the script file in the **Main python file** field: `s3a://<input_bucket_name>/scripts/obtain-table.py`.
    1. Wait for the job to complete and make sure the output bucket contains the `csv` folder with a table in CSV format.

{% endlist %}

## 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:

1. [Delete the Apache Hive™ Metastore cluster](../operations/metastore/cluster-delete.md).
1. [Delete the objects](../../storage/operations/objects/delete.md) from the buckets.
1. Delete the rest of the resources depending on how you created them:

    {% list tabs group=instructions %}

    - Manually {#manual}

        1. [Yandex Data Processing clusters](../../data-proc/operations/cluster-delete.md).
        1. [Object Storage buckets](../../storage/operations/buckets/delete.md).
        1. [Subnet](../../vpc/operations/subnet-delete.md).
        1. [Route table](../../vpc/operations/delete-route-table.md).
        1. [NAT gateway](../../vpc/operations/delete-nat-gateway.md).
        1. [Cloud network](../../vpc/operations/network-delete.md).
        1. [Service account](../../iam/operations/sa/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 %}

_Apache® and [Apache Hive™](https://hive.apache.org/) are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries._