[Yandex Cloud documentation](../../index.md) > [Yandex Data Processing](../index.md) > [Tutorials](index.md) > Working with jobs > Running Apache Hive jobs # Running Apache Hive jobs [Hive](https://hive.apache.org/) is a tool for accessing data storage in the Hadoop ecosystem. It allows you to work with data in different formats and DBMS's using an SQL-like query language. It is mainly used for working with data in HDFS, HBase, S3-compatible storage, and relational DBMS’s. You can run Hive jobs from the [Yandex Cloud CLI](#run-hive-job-cli) and directly on the server using the [Hive CLI](#hive-shell). {% note info %} You can view the job logs and search data in them using [Yandex Cloud Logging](../../logging/index.md). For more information, see [Working with logs](../operations/logging.md). {% endnote %} ## Working with jobs in the Yandex Cloud CLI {#run-hive-job-cli} If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install). Jobs are run from the Yandex Cloud CLI with the help of a Yandex Data Processing agent installed on the cluster master host. The agent gets job properties through the [Yandex Data Processing API](../api-ref/Job/index.md). The executable and its dependencies must reside in a storage accessible to the Yandex Data Processing cluster's service account. The application itself must have access to the storage containing the source data and execution results. There are two ways to send an SQL query to Hive: * [In the job run command](#sql-in-cli). * [In the Object Storage object](#sql-from-objstorage) the Yandex Data Processing cluster service account has read access to. The query execution result is saved to an Yandex Object Storage bucket linked to the cluster together with the service output. ### Getting started {#before-you-begin-yc-cli} 1. [Create a service account](../../iam/operations/sa/create.md) with the `dataproc.agent` and `dataproc.provisioner` roles. 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 Yandex Data Processing cluster](../operations/cluster-create.md) with the following settings: * **Services**: * `HDFS` * `HIVE` * `MAPREDUCE` * `SPARK` * `YARN` * **Service account**: Select the service account you created earlier. * **Bucket name**: Select a bucket for the processing results. * **Public access**: Enable this option to access hosts of all subclusters. {% note info %} Public access to cluster hosts is required if you plan to connect to the cluster via the internet. This connection option is simpler and is recommended for the purposes of this guide. You can connect to non-public hosts as well but only from Yandex Cloud virtual machines located in the same cloud network as the cluster. {% endnote %} ### Providing an SQL query in the job run command {#sql-in-cli} 1. Create an external table for the data from the example in Parquet format. The table will contain a list of flights between US cities in 2018. Run the following query using the Yandex Cloud CLI: ```bash yc dataproc job create-hive \ --cluster-id= \ --name=task-one \ --query-list="CREATE EXTERNAL TABLE flights ( Year bigint, Month bigint, FlightDate string, Flight_Number_Reporting_Airline bigint, OriginAirportID bigint, DestAirportID bigint) STORED AS PARQUET LOCATION 's3a://yc-mdb-examples/dataproc/example01/set01';" ``` {% cut "Example of a response to a query" %} ```bash done (14s) id: c9qloj5crovu******** cluster_id: c9qkjos5sa3d******** created_at: "2024-12-04T03:51:03.285819Z" started_at: "2024-12-04T03:51:15.884426Z" finished_at: "2024-12-04T03:51:15.884426Z" name: task-one created_by: ajefhe0o8uas******** status: DONE hive_job: query_list: queries: - CREATE EXTERNAL TABLE flights (Year bigint, Month bigint, FlightDate string, Flight_Number_Reporting_Airline bigint, OriginAirportID bigint, DestAirportID bigint) STORED AS PARQUET LOCATION 's3a://yc-mdb-examples/dataproc/example01/set01'; application_info: {} ``` {% endcut %} 1. Make sure the table was successfully created by querying the number of flights by month: ```bash yc dataproc job create-hive \ --cluster-id= \ --name=task-two \ --query-list="SELECT Month, COUNT(*) FROM flights GROUP BY Month;" ``` {% cut "Response example:" %} ```bash done (34s) id: c9quejacclo3******** cluster_id: c9qkjos5sa3d******** created_at: "2024-12-04T05:15:38.436203Z" started_at: "2024-12-04T05:16:11.608422Z" finished_at: "2024-12-04T05:16:11.608422Z" name: task-two created_by: ajefhe0o8uas******** status: DONE hive_job: query_list: queries: - SELECT Month, COUNT(*) FROM flights GROUP BY Month; application_info: {} ``` {% endcut %} 1. In the processing results bucket, go to the following folder: ``` dataproc/clusters//jobs/ ``` You can find the job ID in the YC CLI job execution command output and in the API response to the job execution. 1. Download the `driveroutput.000000000` file containing the response to your query. {% cut "Response example:" %} ```bash Init job c9quejacclo3******** at Wed Dec 4 05:15:40 UTC 2024 ... Connecting to jdbc:hive2://localhost:10000 Connected to: Apache Hive (version 3.1.2) Driver: Hive JDBC (version 3.1.2) Transaction isolation: TRANSACTION_REPEATABLE_READ 0: jdbc:hive2://localhost:10000> SELECT Month, COUNT(*) FROM flights GROUP BY Month;; +--------+---------+ | month | _c1 | +--------+---------+ | 1 | 570118 | | 2 | 520731 | | 3 | 611987 | | 4 | 596046 | | 5 | 616529 | | 6 | 626193 | | 7 | 645299 | | 8 | 644673 | | 9 | 585749 | | 10 | 616101 | | 11 | 586178 | | 12 | 593842 | +--------+---------+ 12 rows selected (27.532 seconds) 0: jdbc:hive2://localhost:10000> 0: jdbc:hive2://localhost:10000> Closing: 0: jdbc:hive2://localhost:10000 ``` {% endcut %} If the query runs with errors, the `driveroutput.000000000` file will contain this information as well. ### Providing an SQL query in an Object Storage object {#sql-from-objstorage} 1. Create a file named `create-table.sql` and put an SQL query into it to create an external table for the example data in Parquet format. The table will contain a list of flights between US cities in 2018. Your SQL query will look as follows: ```sql CREATE EXTERNAL TABLE flights ( Year bigint, Month bigint, FlightDate string, Flight_Number_Reporting_Airline bigint, OriginAirportID bigint, DestAirportID bigint) STORED AS PARQUET LOCATION 's3a://yc-mdb-examples/dataproc/example01/set01'; ``` 1. [Upload](../../storage/operations/objects/upload.md) the `create-table.sql` file to the input data bucket. 1. Run this command: ```bash yc dataproc job create-hive \ --cluster-id= \ --name=task-one \ --query-file-uri="s3a:///create-table.sql" ``` {% cut "Response example:" %} ```bash done (8s) id: c9qhpt6334qs******** cluster_id: c9qkjos5sa3d******** created_at: "2024-12-04T04:21:20.062704Z" started_at: "2024-12-04T04:21:27.702644Z" finished_at: "2024-12-04T04:21:27.702644Z" name: task-one created_by: ajefhe0o8uas******** status: DONE hive_job: query_file_uri: s3a:///create-table.sql application_info: {} ``` {% endcut %} 1. Create a file named `get-data.sql` and put an SQL query into it for the number of flights by month: ```sql SELECT Month, COUNT(*) FROM flights GROUP BY Month; ``` 1. Upload the `get-data.sql` file to the input data bucket. 1. Run this command: ```bash yc dataproc job create-hive \ --cluster-id= \ --name=task-two \ --query-file-uri="s3a:///get-data.sql" ``` {% cut "Response example:" %} ```bash done (37s) id: c9q2srq817cu******** cluster_id: c9qkjos5sa3d******** created_at: "2024-12-04T04:24:58.480468Z" started_at: "2024-12-04T04:25:34.613549Z" finished_at: "2024-12-04T04:25:34.613549Z" name: task-two created_by: ajefhe0o8uas******** status: DONE hive_job: query_file_uri: s3a:///get-data.sql application_info: {} ``` {% endcut %} 1. In the processing results bucket, go to the following folder: ``` dataproc/clusters//jobs/ ``` You can find the job ID in the YC CLI job execution command output and in the API response to the job execution. 1. Download the `driveroutput.000000000` file containing the response to your query. {% cut "Response example:" %} ```bash Init job c9q2gha5hocg******** at Wed Dec 4 06:56:45 UTC 2024 ... Connecting to jdbc:hive2://localhost:10000 Connected to: Apache Hive (version 3.1.2) Driver: Hive JDBC (version 3.1.2) Transaction isolation: TRANSACTION_REPEATABLE_READ 0: jdbc:hive2://localhost:10000> SELECT Month, COUNT(*) FROM flights GROUP BY Month; +--------+---------+ | month | _c1 | +--------+---------+ | 1 | 570118 | | 2 | 520731 | | 3 | 611987 | | 4 | 596046 | | 5 | 616529 | | 6 | 626193 | | 7 | 645299 | | 8 | 644673 | | 9 | 585749 | | 10 | 616101 | | 11 | 586178 | | 12 | 593842 | +--------+---------+ 12 rows selected (28.801 seconds) 0: jdbc:hive2://localhost:10000> Closing: 0: jdbc:hive2://localhost:10000 ``` {% endcut %} If the query runs with errors, the `driveroutput.000000000` file will contain this information as well. ## Working with jobs in the Hive CLI {#hive-shell} ### Getting started {#before-you-begin-hive-shell} 1. [Create a service account](../../iam/operations/sa/create.md) with the `dataproc.agent` and `dataproc.provisioner` roles. 1. [Create a Yandex Data Processing cluster](../operations/cluster-create.md) with the following settings: * **Services**: * `HDFS` * `HIVE` * `SPARK` * `YARN` * **Service account**: Select the service account you created earlier. * **Public access**: Enable this option to access hosts of all subclusters. {% note info %} Public access to cluster hosts is required if you plan to connect to the cluster via the internet. This connection option is simpler and is recommended for the purposes of this guide. You can connect to non-public hosts as well but only from Yandex Cloud virtual machines located in the same cloud network as the cluster. {% endnote %} ### Running jobs using the Hive CLI {#run-job-hive-shell} 1. [Connect to the master host](../operations/connect-ssh.md) over SSH and run the `hive` command. 1. Test Hive by running the `select 1;` command. The correct result looks like this: ```text OK 1 Time taken: 0.077 seconds, Fetched: 1 row(s) ``` 1. Create an external table for the data from the example in Parquet format. The table will contain a list of flights between US cities in 2018. Run the following query in the Hive CLI: ```sql CREATE EXTERNAL TABLE flights ( Year bigint, Month bigint, FlightDate string, Flight_Number_Reporting_Airline bigint, OriginAirportID bigint, DestAirportID bigint) STORED AS PARQUET LOCATION 's3a://yc-mdb-examples/dataproc/example01/set01'; ``` 1. Check the list of tables: ```sql show tables; ``` 1. The list of tables should look like this: ```text OK flights Time taken: 0.043 seconds, Fetched: 1 row(s) ``` 1. Query the number of flights by month: ```sql SELECT Month, COUNT(*) FROM flights GROUP BY Month; ``` {% cut "Response example:" %} ```text Query ID = root_20200119195338_28049b67-4de9-4568-a4c4-3bbe******** Total jobs = 1 Launching Job 1 out of 1 Status: Running (Executing on YARN cluster with App id application_157925157****_****) ---------------------------------------------------------------------------------------------- VERTICES MODE STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED ---------------------------------------------------------------------------------------------- Map 1 .......... container SUCCEEDED 6 6 0 0 0 0 Reducer 2 ...... container SUCCEEDED 1 1 0 0 0 0 ---------------------------------------------------------------------------------------------- VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 11.52 s ---------------------------------------------------------------------------------------------- OK 1 570118 2 520731 3 611987 4 596046 5 616529 6 626193 7 645299 8 644673 9 585749 10 616101 11 586178 12 593842 Time taken: 12.137 seconds, Fetched: 12 row(s) ``` {% endcut %}