The introduction
Hello everyone, I am ChinaManor, which literally translates to Chinese code farmer. I hope I can become a pathfinder on the road of national rejuvenation, a ploughman in the field of big data, an ordinary person who is unwilling to be mediocre.
A quick start to grasp a new big data component and help novices understand big data technology. The following is a series of articles:
Article portal:
What is Kudu
A quick look at Elastic Search
A quick look at ClickHouse Battle Nation’s open source search engine (super detail + Quick Start)
SuperSet SuperSet SuperSet SuperSet
This is the fifth article in the series of quick To understand: fast big data ETL magic Kettle
Programmer little sister’s trouble
The company came to a beautiful programmer little sister called floret, she just graduated from the university, the project manager arranged her such a job:
Data extraction requirements
The user data needs to be extracted from Excel to MySQL using Kettle
The preparatory work
To complete this case, we need to prepare the following:
Find little Sister’s Excel file
You can find the user.xlsx file in the Data/test data folder
4.3.3 Loading the MySQL Driver in the Kettle File To connect the kettle file to the MySQL file, install the MySQL driver. This is similar to installing the graphics card driver after installing the OS. Load the MySQL driver in the following two steps: 1. Import the MySQL JDBC driver packages mysql-connector-java-5.1.47.jar and mysql-connector-java-8.0.13.jar into the data-integration/lib file
3. Restart the Kettle
Build a Kettle data flow diagram
Effect:
(3) Shortcut key: Ctrl + N
2. From the core object on the left, drag “Input /Excel Input” and “Output/table Output” components into the middle area
3. Then hold down the Shift key, click the left mouse button on the “Excel Input” component, drag the “Table Output” component, connect the two components, and the data flow diagram is built
Configure components in the Kettle data flow diagram
Since the data flow diagram has been built, can Kettle extract data from Excel files into MySQL? Obviously not. Kettle doesn’t know which Excel file to extract data into which MySQL. We need to configure these two components to tell Kettle which Excel file to extract from and which MySQL to load the data into. 4.5.1 Configuring the Excel Input Component 1. Double-click the Excel input component, a dialog box will pop up, and you can configure the component in the dialog box
2. Specify the Excel file to be extracted because the Excel file is Excel 2007Table typeExcel 2007 XLSX (Apache POI)
3. Then we need to find the Excel file to extract, click the “Browse” button to find the “data/test data /user.xlsx” file
4. Click the “Add” button next to it again, remember: be sure to click the add button! Otherwise it won’t work!
5. In the pop-up menu, click on the “Sheet1” workbook and click the “>” button to move to the right.
6. Click the Fields TAB and click Get Fields from header data… The Kettle button reads the first row of field names from Excel.
7. Set the age field format to # and register_date format to YYYY-MM-DD HH: MM :ss.
8. Click the “Preview record” button to view the extracted data.
9. Click “OK” button to save.
4.5.2 Configuring the MySQL Component 4.5.2.1 Creating a Database Connection To use the Kettle to operate the MySQL database, you must establish a connection between the Kettle and MySQL. Otherwise, the Kettle does not know which MySQL library to operate. 1. Double-click the “Table Input” component, and the configuration window will pop up automatically. Click “New”
(1) Enter the connection name, starting with mysql_ and ending with the database name keTTLE_demo. (2) Select MySQL from the connection type list. (3) Enter the connection mode
3. Click Test to check whether the Kettle is connected to MySQL 4 correctly. Click Ok to save, and at this point the database connection should be created.
4.5.2.2 Using the Kettle To Automatically Create a table in MySQL Before storing data in MySQL, you must create a table. So, do we need to manually create a table in MySQL to hold the data extracted from Excel? The answer is: no. Kettke can automatically create tables for us in MySQL. 1. Set the target table name to t_user. The next Kettle table is created in the MySQL database.
2. Click SQL in the lower part to see that the Kettle automatically generates SQL statements for creating tables in MySQL
Set the age and gender field types to INT
3. Click the Execute button. The Kettle will cause MySQL to execute the SQL script. After the command is executed, you can refresh the database in DataGrip and view the T_user table created by Kettle.
4. Click OK to save the configuration
Save the Settings and start the Kettle conversion
Now that the components in the data flow diagram are ready, you can start the Kettle conversion. 1. Click the Save button to save the conversion.
2. Click the Play button on the toolbar to start the execution.
3. After the command is executed successfully, the following page is displayed. The component displays a green check mark, and you can see in the execution result: Conversion completed! The Kettle conversion has been successfully executed.
Confirm execution results
Has Kettle helped us extract and load data from Excel into MySQL? We need to go to MySQL and see if there’s any data in the T_user table. Double-click the T_user table in DataGrip to see that the data in Excel has been extracted to MySQL.
Here, congratulations to you, programmer little sister floret problem you have helped her to solve, evening let her ask you to eat a meal.
conclusion
The above is a quick start of the big data ETL artifact Kettle(XLS import mysql), data and software can be private I get ~
May you have your own harvest after reading, if there is a harvest might as well a key three even once ~