{ "cells": [ { "cell_type": "markdown", "id": "858522da", "metadata": {}, "source": [ "# Connect to Hive, Presto & Trino Engine using `Python3`\n", "\n", "\n", "#### Topics covered in this example\n", "* Installing `python3-devel` and `cyrus-sasl-devel` on the EMR master node\n", "* Installing python libraries on the Amazon EMR cluster\n", "* Connecting to Hive using Python3 `PyHive` library\n", "* Connecting to Presto using Python3 `PyHive` library\n", "* Connecting to Trino using Python3 `PyHive` library" ] }, { "cell_type": "markdown", "id": "f3e440f3", "metadata": {}, "source": [ "## Table of Contents:\n", "\n", "1. [Prerequisites](#Prerequisites)\n", "2. [Introduction](#Introduction)\n", "3. [Install dependency libraries](#Install-dependency-libraries)\n", "4. [Connect to Hive using `PyHive`](#Connect-to-Hive-using-PyHive)\n", "5. [Connect to Presto using `PyHive`](#Connect-to-Presto-using-PyHive)\n", "6. [Connect to Trino using `PyHive`](#Connect-to-Trino-using-PyHive)" ] }, { "cell_type": "markdown", "id": "7a960666", "metadata": {}, "source": [ "***\n", "\n", "## Prerequisites\n", "
\n", "NOTE : In order to execute this notebook successfully as is, please ensure the following prerequisites are completed.
\n", "\n", "* This example installs python3 libraries, hence the EMR cluster attached to this notebook must have internet connectivity.\n", "* The EMR cluster attached to this notebook should have the following packages installed:\n", " * `python3-devel`\n", " * `cyrus-sasl-devel`\n", " \n", "* You can do this by running the following command on EMR mater node:\n", "

sudo yum install -y python3-devel cyrus-sasl-devel

\n", "* This notebook uses the `Python3` kernel.\n", "***" ] }, { "cell_type": "markdown", "id": "d1039545", "metadata": {}, "source": [ "## Introduction\n", "In this example we use `Python3` to connect to a table in Hive, Presto and Trino using `PyHive`.\n", "\n", "PyHive is a collection of Python DB-API and SQLAlchemy interfaces for Presto and Hive.\n", "***" ] }, { "cell_type": "markdown", "id": "91ae5583", "metadata": {}, "source": [ "## Install dependency libraries\n", "\n", "`%pip install` is the same as `!/emr/notebook-env/bin/pip install` and are installed in `/home/emr-notebook/`.\n", "\n", "After installation, these libraries are available to any user running an EMR notebook attached to the cluster. Python libraries installed this way are available only to processes running on the master node. The libraries are not installed on core or task nodes and are not available to executors running on those nodes." ] }, { "cell_type": "code", "execution_count": null, "id": "b7eaed37", "metadata": {}, "outputs": [], "source": [ "%pip install pyhive thrift sasl thrift_sasl" ] }, { "cell_type": "markdown", "id": "a7b2d044", "metadata": {}, "source": [ "## Connect to Hive using `PyHive`\n", "\n", "We will connect to Hive using `PyHive` library. Please make sure you replace the values for `hostName, userName, and databaseName` as applicable to your environment. The Hiveserver2 port is set to default 10000.\n", "\n", "In this example, we are connecting to Hive database `default` running locally on the EMR master node. We will query the table `hive_sample_table` to retrieve some values." ] }, { "cell_type": "code", "execution_count": null, "id": "4e55fcc3", "metadata": {}, "outputs": [], "source": [ "from pyhive import hive\n", "import getpass\n", "\n", "hostName = \"127.0.0.1\"\n", "userName = \"hadoop\"\n", "userPassword = getpass.getpass('Enter hive user password')\n", "databaseName = \"default\"\n", "hivePort = 10000\n", "\n", "def hiveConnection(hostName, hivePort, userName, userPassword, databaseName):\n", " conn = hive.connect(host=hostName,\n", " port=hivePort,\n", " username=userName,\n", " password=userPassword,\n", " database=databaseName,\n", " auth='CUSTOM')\n", " cur = conn.cursor()\n", " cur.execute('SELECT id FROM hive_sample_table LIMIT 3')\n", " result = cur.fetchall()\n", "\n", " return result\n", "\n", "# Call above function\n", "tableData = hiveConnection(hostName, hivePort, userName, userPassword, databaseName)\n", "\n", "# Print the results\n", "print(tableData)" ] }, { "cell_type": "markdown", "id": "3123417c", "metadata": {}, "source": [ "## Connect to Presto using `PyHive`\n", "\n", "We will connect to Presto using `PyHive` library. Please make sure you replace the values for `hostName, userName, schemaName and catalogName` as applicable to your environment. The port is set to EMR default of 8889.\n", "\n", "In this example, we are connecting to `default` schema/database stored inside the `hive` catalog on the EMR master node. We will query the table `presto_sample_table` using within the `default` schema to retrieve some values.\n", "\n", "The connection uses HTTP protocol for Presto. You can enabled SSL/TLS and configure LDAPS for Presto on Amazon EMR by referring to the documentation [here](https://docs.aws.amazon.com/emr/latest/ReleaseGuide/presto-ssl.html)" ] }, { "cell_type": "markdown", "id": "d36c7db7", "metadata": {}, "source": [ "Presto authentication and without authentication and getPass #####" ] }, { "cell_type": "code", "execution_count": null, "id": "8d28fec1", "metadata": {}, "outputs": [], "source": [ "from pyhive import presto\n", "import requests\n", "\n", "hostName = \"127.0.0.1\"\n", "userName = \"hadoop\"\n", "schemaName = \"default\"\n", "catalogName = \"hive\"\n", "prestoPort = 8889\n", "\n", "headers = {\n", " 'X-Presto-User': userName,\n", " 'X-Presto-Schema': schemaName,\n", " 'X-Presto-Catalog': catalogName\n", "}\n", "\n", "prestoSession = requests.Session()\n", "prestoSession.headers.update(headers)\n", "\n", "def prestoConnection(prestoSession, hostName, prestoPort):\n", " conn = presto.connect(requests_session=prestoSession,\n", " host=hostName,\n", " port=prestoPort\n", " )\n", "\n", " cur = conn.cursor()\n", " cur.execute('SELECT id FROM presto_sample_table LIMIT 3')\n", " result = cur.fetchall()\n", "\n", " return result\n", "\n", "# Call above function\n", "tableData = prestoConnection(prestoSession, hostName, prestoPort)\n", "\n", "# Print the results\n", "print(tableData)" ] }, { "cell_type": "markdown", "id": "375cc99d", "metadata": {}, "source": [ "## Connect to Trino using `PyHive`\n", "\n", "We will now connect to Trino using `PyHive` library. Please make sure you replace the values for `hostName, userName, schemaName and catalogName` as applicable to your environment. The port is set to EMR default of 8889.\n", "\n", "In this example, we are connecting to `default` schema/database stored inside the `hive` catalog on the EMR master node. We will query the table `trino_sample_table` using within the `default` schema to retrieve some values.\n", "\n", "The connection uses HTTP protocol for Presto. You can enabled SSL/TLS and configure LDAPS for Presto on Amazon EMR by referring to the documentation [here](https://docs.aws.amazon.com/emr/latest/ReleaseGuide/presto-ssl.html)" ] }, { "cell_type": "code", "execution_count": null, "id": "54d94b87", "metadata": {}, "outputs": [], "source": [ "from pyhive import trino\n", "import requests\n", "\n", "hostName = \"127.0.0.1\"\n", "userName = \"hadoop\"\n", "schemaName = \"default\"\n", "catalogName = \"hive\"\n", "trinoPort = 8889\n", "\n", "## Starting with Trino release 0.351 rename client protocol headers to start with X-Trino- instead of X-Presto-\n", "headers = {\n", " 'X-Presto-User': userName,\n", " 'X-Presto-Schema': schemaName,\n", " 'X-Presto-Catalog': catalogName\n", "}\n", "\n", "trinoSession = requests.Session()\n", "trinoSession.headers.update(headers)\n", "\n", "def trinoConnection(trinoSession, hostName, trinoPort):\n", " conn = trino.connect(requests_session=trinoSession,\n", " host=hostName,\n", " port=trinoPort\n", " )\n", "\n", " cur = conn.cursor()\n", " cur.execute('SELECT id FROM trino_sample_table LIMIT 3')\n", " result = cur.fetchall()\n", "\n", " return result\n", "\n", "# Call above function\n", "tableData = trinoConnection(trinoSession, hostName, trinoPort)\n", "\n", "# Print the results\n", "print(tableData)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.4" } }, "nbformat": 4, "nbformat_minor": 5 }