How to Create an S3 External Table in Amazon EMR with Remote Metastore

Amazon Elastic MapReduce (EMR) is a powerful tool for processing and analyzing big data. But, to get the most out of it, you need to know how to set up external tables on Amazon S3. This is particularly useful when using a remote metastore, allowing you to use your data across different clusters and instances.

By Saturn Cloud | Tuesday, August 08, 2023 | Miscellaneous

How to Create an S3 External Table in Amazon EMR with Remote Metastore

Let’s go through the process of creating an S3 external table in Amazon EMR with a remote metastore.

Prerequisites

Before starting, ensure you have:

A running Amazon EMR cluster
An S3 bucket with your data
A running remote metastore

Step 1: Connect to the Master Node

Start by connecting to the master node of your EMR cluster. You can do this using SSH. The general syntax is as follows:

ssh -i /path/my-key-pair.pem hadoop@my-master-node-public-dns-name

Step 2: Start Hive

Once you’ve connected, start the Hive shell:

hive

Step 3: Connect to Your Remote Metastore

To connect to your remote metastore, use the following Hive command:

CREATE DATABASE IF NOT EXISTS my_database
LOCATION 's3://my-bucket/my-database/';

Replace my_database with the name of your database and s3://my-bucket/my-database/ with the path to your S3 bucket.

Step 4: Create the External Table

The next step is to create the external table in your remote metastore. Here’s the general syntax:

CREATE EXTERNAL TABLE IF NOT EXISTS my_database.my_table
(
  id int,
  name string,
  age int
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://my-bucket/my-table/';

Replace my_database.my_table with the name of your database and table, and s3://my-bucket/my-table/ with the path to your S3 bucket.

Step 5: Verify the Table Creation

To verify that your table is created, use the following command:

SHOW TABLES in my_database;

The output should include my_table.

Conclusion

Creating an S3 external table in Amazon EMR with a remote metastore is a straightforward process. It allows you to use your data across different clusters and instances, making your big data processing and analysis more flexible and efficient.

Remember to replace the placeholders in the commands with your specific details. As a data scientist or software engineer, understanding how to create an external table and connect to a remote metastore are essential skills for using Amazon EMR effectively.

If you found this guide helpful, make sure to share it with your colleagues and stay tuned for more “How to” guides for the technical audience of data scientists. Happy data processing and analyzing!

Keywords: Amazon EMR, S3 External Table, Remote Metastore, Data Science, Big Data, Amazon S3, Hive, Database

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Join today and get 150 hours of free compute per month.

Try Saturn Cloud Now