Automate Data Extraction with SQL and Sqoop on AWS

Review in this article how to transfer data from a relational database with limited permissions to a file system with unlimited permissions - Skoop.io

Companies need to extract large amounts of data from different databases, transform and enrich them so that they generate added value to the business.

The extraction process is often complex to develop, because, in some cases, the provider of that data provides access only to views and generally limited access to the database. This makes sense for safety and good practices. Now, how do we extract data with limited access to the vendors' database? What alternatives do we have? How do we develop the extraction in such a way that it works independently of the database engine? Keep reading.

Basic Concepts.

Before we begin, we'll review some concepts about big data and the Apache Sqoop, Apache Hadoop, Aws EMR and Aws DynamoDB tools.

Big Data: It is a term that describes the large volume of data, both structured and unstructured, that flood businesses every day. But it's not the amount of data that's important. What matters with Big Data is what organizations do with the data. Big Data can be analyzed to obtain ideas that lead to better decisions and strategic business movements. Fountain https://www.powerdata.es/big-data

Apache Sqoop: It is a tool designed to efficiently transfer massive data between Apache Hadoop and structured data stores such as relational databases or vice versa. Source: https://sqoop.apache.org/

Apache Hadoop: It is an open source framework that allows the distributed storage and processing of large data sets based on commercial hardware. Within Apache Hadoop there is HDFS (Hadoop File System), which is responsible for storing large amounts of data with horizontal scaling, using MapReduce for the processing of these data. Source: https://blog.powerdata.es/el-valor-de-la-gestion-de-datos/bid/397377/qu-es-el-apache-hadoop

AWS EMR: Amazon EMR is a managed cluster platform that simplifies the execution of Big Data frameworks, such as Apache Hadoop and Apache Spark on AWS to process and analyze large amounts of data. Source: https://docs.aws.amazon.com/es_es/emr/latest/ManagementGuide/emr-what-is-emr.html

AWS DynamoDB: It is a non-relational key-value database. Source: https://aws.amazon.com/es/dynamodb/

Now that we know these concepts in a general way, I will explain how we use these tools in a solution that we call “Skoop-io”.

There are different database extraction alternatives for big data, but the one that best suits our requirements is sqoop, thanks to the fact that this is a tool specialized in data extraction and is also open source. We developed Skoop-io based on the sqoop extraction tool.

Skoop-io is a program capable of extracting records from different database engines, regardless of their limitations. The processing is automatic and the only manual thing is to configure the credentials and the type of processing (full, partial or incremental).

Types of loads

Skoop-io currently has 3 types of load, fully configurable for importing data to an S3 bucket on AWS.

Full: It obtains all the data from the database and leaves it in the S3 bucket in avro format.

Partial: Get all the data using a date-type field from the last few months that are configured.

Incremental: Get non-existent records in our S3 storage for each query to skoop-io.

Architecture.

To carry out Skoop-io, we use AWS resources, specifically EMR as a cluster for the execution of the program, DynamoDB for the data import configuration and S3 for the storage of imported data.

Thanks to Big Data tools and cloud resources (AWS in this case), we were able to quickly implement a stable relational data extraction solution, independent of database engines or limited access from providers.

Ready to optimize your data extraction processes?

At Kranio, we have the experience and tools necessary to help you implement efficient data extraction and processing solutions using technologies such as Sqoop and AWS. Contact us and discover how we can promote the digital transformation of your company.