This guide shows you how to configure Kettle to import a local Excel file into an AnalyticDB for MySQL cluster.
Background information
Kettle is a popular open source extract-transform-load (ETL) tool that is used for data collection, conversion, and migration. Kettle supports not only various relational databases and NoSQL databases such as HBase and MongoDB, but also niche data sources such as Microsoft Office Excel and Access. Kettle can support more data sources by using extensions and plug-ins.
Prerequisites
Before you begin, ensure that you have:
Kettle installed on your local machine
A database and a table created in your AnalyticDB for MySQL cluster
The IP address of the Kettle machine added to a cluster whitelist. See Configure an IP address whitelist
(Optional) A public endpoint, if you are connecting over the Internet. See Apply for or release a public endpoint
When configuring the database connection in step 2, do not select Use Result Streaming Cursor.
Import data from Excel
Step 1: Create a conversion
Start Kettle, then go to File > New > Conversion to create a conversion task.
Step 2: Create a database connection
Go to File > New > Database Connection. Configure the following parameters:
| Parameter | Description |
|---|---|
| Connection Name | A descriptive name that uniquely identifies this connection |
| Connection Type | Select MySQL |
| Access | Select Native (JDBC) |
| Host Name | The endpoint of your AnalyticDB for MySQL cluster |
| Database Name | The name of your AnalyticDB for MySQL database |
| Port Number | The port used to connect to the cluster. Default: 3306 |
| User Name | A privileged account or standard account for the cluster |
| Password | The password of the account |
To find your cluster endpoint, go to the Cluster Information page in the AnalyticDB for MySQL console. See Connect to an AnalyticDB for MySQL cluster.
Do not select Use Result Streaming Cursor.
After filling in the parameters, click Test. In the Database Connection Test dialog box, follow the prompts to verify if the connection to the AnalyticDB for MySQL database is successful. After the connection passes the test, click OK.
Step 3: Add an Excel Input step
In the Core objects tab on the left, go to Input > Excel Input. Drag Excel Input into the workspace.
Double-click Excel Input to open its settings. Click Browse and Add to add your Excel file to Selected Files. Configure the Worksheet, Content, and Field tabs as needed, then click Preview to verify the file contents look correct.
Step 4: Add a Table Output step
In the Core objects tab, go to Output > Table Output. Drag Table Output into the workspace.
Step 5: Connect the steps
Draw a connection line from Excel Input to Table Output.
Step 6: Configure the Table Output step
Double-click Table Output to open its settings. Configure the following:
Target Schema: The name of your AnalyticDB for MySQL database
Target Table: The name of the target table in the database
Select Specify database fields
Select Use batch update for inserts
On the Database fields tab, click Get fields and then Enter field mapping to map columns in your Excel file to columns in the AnalyticDB for MySQL table.
Step 7: Run the conversion
Click the white arrow to run the conversion. Monitor the operation logs and status to track progress.
After the conversion completes, the data from your Excel file is available in AnalyticDB for MySQL for analysis.