Concept
For data ingestion and synchronization into a big data environment, deployments face two challenges: a fast initial load of data that requires parallelization, and the ability to incrementally load new data as it arrives without having to reload the full table. While tools like Sqoop support parallelization for data ingest to get data from legacy sources into a data lake, you need an expert to make it work. How do you partition the data? Do you need to run 10 mappers or 20, how do you know? If you can't correctly parallelize the ingest of data, ingestion tasks that could be done in an hour can take up to 10 to 20 times longer. The problem is that only hard-to-find experts know how to tune the data ingestion process for proper performance. Infoworks automates all of the steps involved in loading data efficiently into your data lake.
Additionally, most organizations aren’t moving their entire operations onto a big data environment. They move data there from existing operational systems to perform new kinds of analysis or machine learning. It means they need to keep loading new and changed data as it arrives. The problem is that these big data environments don’t support the concept of adds, deletes, or inserts. Nor do they provide support for schema changes made to the source systems. As a result, you have to reload the entire data set again and modify your downstream data transformation processes, or you have to code your way around these classic change data capture problems. Infoworks automates this entire process.
Objectives
In this session, you will:
Crawl a sales database’s metadata
Explore the schema and identify the tables to ingest
Configure tables and ingest data on to HDFS/Hive
Crawl weather CSV file
Verify that Infoworks discovers the data types from the CSV file
Ingest the data and view the weather data on HDFS/Hive
Source Database And Data File Used In This Session
Relational Database: SalesDB (Oracle) – sample sales dataset.
Flat file: Weather (CSV) – daily weather information (temperature, weather conditions etc.) for various cities.
Prerequisites
Before you ingest data, an admin must create a source for you. Since Infoworks is a multi-user, collaborative platform, only admins can create sources and assign a Hive schema and HDFS base path to the source.
For this session, an admin has already created your source. Refer to your “Welcome to Infoworks” email to complete the steps below.
Data Source: Relational Database (Sales DB)
Step 1: Navigate To Sales Source
Click the Sources tab.
Click the SalesDB source that has been set up for you, and is named in your “Welcome to Infoworks” email. You can either scroll down the list or use the search box to find the source, then click on it. Typically, the source will be called SalesDB_.
Click on your sales database source.
Step 2: Configure Connection To Source Database
See the screenshot below for the location of these commands.
Click the Settings Icon.
Enter the settings for the SalesDB source: (see your “Welcome to Infoworks” email for items in )
Fetch Data Using: JDBC
Connection URL:
Username: training_user
Click to change Password and enter:
Source Schema: SALESDB_TRAINING
Click Save Settings.
Click Test Connection to test connectivity to the source database.
The Fetch Data Using dropdown allows the user to select the transport mode. Some databases provide native connectors that may be faster than JDBC, like Teradata’s TPT. The user details used to connect to the database must have read permission to the source schema for Infoworks to ingest data.
Now view the results:
Click the Ingestion Logs icon
select the Test Connection job to view its logs. You may have to wait a few seconds for the job to be completed.
All jobs related to this source will be displayed in this screen.
If the connection fails; verify the connection parameters and test the connection again.
Step 3: Crawl Metadata
Now that we have tested the connectivity to the source database; let’s crawl the metadata. (List of tables in the database schema)
Click the Source Configurations icon.
Click Crawl Metadata and click the Yes crawl metadata button.
Click the Ingestion Logs icon and select the Fetch Metadata job to view its logs.
Wait for a few seconds for the job to be completed.
Crawl metadata automatically displays the following information from the database:
list of tables
list of columns for each table
referential integrity between tables
number of rows in each table
indexes and partitioning information to automatically configure some of the ingestion properties
Some of these metadata queries can be configured to not be executed via advanced configurations if required.
Step 4: View Table Configurations And Metadata
Click the Source Configuration icon to view the tables discovered from the source system.
Click the CATEGORIES table to view its configurations.
For the scope of this training, we will not be changing any of these configurations.
On the configuration screen, you can configure various table configuration parameters like Ingestion, Export, Reconciliation, etc. Some of the important ingestion configurations are highlighted in the screenshot.
Click the Source Configuration icon.
For the CATEGORIES table, click the Metadata icon to view the columns that were automatically discovered.
The ingestion type for all tables is set to Full Load by default. The Num. Rows in Source column indicates the row count for each table from the metadata. The Last Ingested Record Value and Last Exec Time fields are blank because these tables have not yet ingested.
Check the list of columns, their data types, and the table metadata identified for this table.
During the metadata crawl job, Infoworks ADE automatically identifies the data types of each column in the source and maps them to the corresponding data type in the target to ensure that the precision of the data is not lost. Infoworks ADE adds multiple audit columns (prefixed with ZIW_) to automatically perform incremental loads and build a historical view of the data as it changes over time.
Step 5: Create Table Group For Ingestion
Table groups allow users to logically group tables and ingest them in parallel.
Ingestion of tables is done by creating a table group or adding them to existing table groups.
A table can be part of multiple table groups.
Click the Source Configuration icon in the left navigation.
Click the Table Groups tab and click the Add Table Group button.
Enter the following details:
Table Group Name: LoadSalesData
Max Connections to Source: 50
Max Parallel Tables: 3
Click the Add Tables button.
Click the header checkbox to select all the tables.
Click the Add Tables button at the bottom of the pop-up window.
Click the Auto allocate link to allocate the % connection quota for each table.
The maximum tables in parallel and the connection quota fields allow you to control how the connections to the database are distributed between tables when they are being ingested in parallel.
By clicking on auto-allocate, Infoworks equally distributes the connections based on the number of parallel tables you have provided. The current configuration indicates that 33% of the connections will be used by each table. When the job runs, it will execute the first 3 tables first. As and when the table ingestions complete, the next table will be picked from the list and ingested.
The sum of connection quotas can exceed 100%. When this happens, the tables will be queued and processed based on the availability of connections.
Accept all of the other default values and click the Save Configuration button at the bottom.
Step 6: Ingest Data From Sales Database
Navigate to the list of Table Groups
Click the View Table Group Configuration icon for the LoadSalesData table group.
On the LoadSalesData model, click Initialize and Ingest (and confirm “Yes, Initialize and Ingest”) to start the ingestion of the table group.
Click the Ingestion Logs icon (the little hammer) in the left navigation and select the Initialize and Ingest job to view its logs.
Wait for 4 to 5 minutes for the job to be completed.
After the ingestion is complete, click the Source Configuration icon to view the list of tables. You will notice that the Num. Rows in Source, Last Ingested Record Value, and Last Exec Date columns are now populated for the ingested tables.
Step 7: View Ingestion Reports For Ingested Tables
Click the Ingestion Reports icon (see screenshot below).
From the Report Type drop-down, choose Table Group Metrics.
Select the table group LoadSalesData.
Click the Apply Filters button.
You can choose to download the report as Excel or CSV.
Congratulations! You have completed your first ingestion and verified the record counts as well!
Data Source: Flat Files (Weather Data)
Before we configure the next source, let us look at the directory where the data is stored and take a look at the first few lines in the file.
If you have access to the Infoworks server, navigate to the source directory where the weather files are uploaded or ask the instructor for the source directory path. In this case, the source directory base path is: /home/infoworks-user /weather_source.
Verify that the base directory contains a directory called weather. Each directory under the source directory can be configured to be mapped to a Hive table under a source.
Open the data file to see and verify that it resembles the following:
Step 1: Navigate To Weather Source
Click the Sources tab.
Click on the Weather source that has been set up for you, and is named in your “Welcome to Infoworks” email. You can either scroll down the list or use the search box to find the source, then click on it. Typically, the source will be called Weather_.
Step 2: Configure Source
Click the Settings icon (see screenshot below).
Since we are loading data from the File System (Infoworks server), select the From Filesystem option.
In the Source Base Path field, enter the path to the directory containing the weather data to be ingested: see your “Welcome to Infoworks” email, under step 9.3 info.
Click the Save Settings button.
Step 3: Configure File Mapping
After specifying the base path where the source files are available, you need to provide a mapping between the directory containing the files and the Hive tables in the Data Lake. If you have multiple files conforming to the same schema and you want to load all of them to the same Hive table, place them in one directory under the source base path.
Navigate to the Settings, scroll down to the File Mappings section
Click Add Entry.
Step 4: Configure Weather File Mapping
Enter the following values in the fields: (some of the fields will be auto-populated, but you should check them)
Table: weather
Hive Table Name: weather
Source Path: /weather
Relative HDFS Target Path: /weather
Number of header rows: 1 (the weather file mentioned above contains 1 header row with the column names)
Column separator: ,
Column Enclosed By: “
Click the Save Entry button.
Step 5: Crawl Metadata
Click the Source Configuration icon.
Click Recrawl Metadata (and confirm “Yes, Crawl Metadata”) to start fetching metadata.
Click the Ingestion Logs icon and click the Fetch Metadata job to view its logs.
Wait for a few seconds for the fetch metadata job to be completed.
Crawl metadata samples the files and automatically configures the following for all tables:
list of columns
column names (if the file mapping is configured to have header rows)
the data type of the rows
Step 6: Validate Schema For Weather Table
The ingestion framework scans through a sample set from the files and infers the list of columns and their datatype. As a source admin, before ingesting the data, it is recommended that you review the columns and data types.
Click the Configure icon for the weather table to view the columns that were automatically discovered.
You can click the Edit Schema button to edit the columns and data types if required (but it’s not required in this exercise). In this area, the user can modify the data type for specific columns in case they were not automatically discovered or are different from what the user intends them to be.
Click the Source Configuration icon to leave without making any changes (since the column mappings do not need to be changed).
Step 7: Create Table Group For Weather
Click the Source Configuration icon.
Click Table Groups
Click Add Table Group
Enter the following:
Table Group Name: LoadWeatherData
Max Parallel Tables: 1
Click the Add Tables button, and then in the pop-up, check the weather table and click Add Table.
Scroll down and click Save Configuration to save the table group configuration.
Step 8: Ingest Data From Weather Files
Navigate to the list of table groups and click LoadWeatherData to view the table group.
In the LoadWeatherData window that pops up, click Initialize and Ingest Now (and confirm with “Yes, Initialize and Ingest”) to start the ingestion of the table group.
Table groups allow users to logically group tables and ingest them in parallel.
Ingestion of tables is done by creating a table group or adding them to existing table groups.
A table can be part of multiple table groups.
Click the Ingestion Logs icon and select the Initialize and Ingest job to view its logs.
Wait approximately 4-5 minutes for the job to be completed.
After the ingestion is complete, click the Source Configuration icon to view the list of tables. You will find that the Num. Rows in Source and Last Exec Time columns are now populated for the ingested tables.
View Ingestion Reports For Ingested Tables
Click the Ingestion Reports icon.
From the Report Type drop-down, choose Table Metrics.
Select the table weather.
Click the Apply Filters button.
You can choose to download the report as excel or CSV if you like.
Congratulations! You have completed ingesting weather data and verified the record counts as well! You can now ingest data from any RDBMS and CSV files into the data lake using Infoworks.
Session Summary
Configured and crawled the metadata of SalesDB from an Oracle database.
Explored the schema and analyzed the data.
Configured tables and ingested the data.
Configured and crawled Weather from CSV files.
Infoworks automatically discovered the data types.
Ingested Weather data.