| ReprintedKettle FAQ (1) Author:Gemini5201314 Abstract: This article mainly introduces some frequently asked questions when using kettle to design some ETL tasks. Most of these questions are not on the official FAQ. You can find answers to some questions on kettle's forum. 1. Join
I get A data stream (whether based on files or databases). A contains field1, field2, and field3 fields. Then I have A data stream B that contains field4, field5, field6, how should I add them now.
This is the easiest place for beginners to make mistakes. Data Stream A can join data streams with data streams B. It must be that they contain join keys, which can be a field or multiple fields. If two data streams do not have join keys, they are cartesian products, which are rarely the same. For example, you need to list the name of an employee and the name of his/her department. If this is in the same database, everyone knows that the where condition will be added to an SQL statement, however, if the employee table and department table are in two different data streams, especially when the source of the data source is multiple databases, we usually use the database join operation, then we use two database table inputs to represent the input stream. One input is the name of the Department table and the other is the name of the employee table. Then we think the two tables can be joined, the output we need is indeed the two fields, but the output of these two fields does not mean that only the input of these two fields is required. A constraint relationship must exist between them. In addition, when performing regular join, merge, update, and delete operations, a compare operation is required first. This compare operation is for compare keys, whether the two tables have the same structure, such as the employee table and department table, they are compared based on the foreign key department_id of the employee. Without the compare key, the two tables cannot be connected .. for two tables, someone may know that it is a direct SQL connection. If there are multiple input data sources and then three tables, someone may be confused. Table A is a field, table B has a field, and Table C has a field, and even the join operation does not exist. The database table output is used directly, and an error is reported. After an error is reported, you can ask experts everywhere, their Database principle teachers are already vomiting blood. If three tables are connected and one SQL statement cannot be done, you need to connect two tables and two tables to get your output after two compare key connections. Remember, your output does not represent your input. the following is a summary:
1. Input a single data source and directly connect it using SQL
2. input multiple data sources (may be text or two or more source databases) and use database join.
3. Output of multiple fields in more than three tables. 2. Kettle Database Connection Mode
Kettle's database connection controls a single database connection in one step, so kettle's connection has a database connection pool, you can specify the number of database connections in the connection pool at the beginning in the specified database connection. The Pooling tab is displayed when you create a database connection, which can specify the maximum number of connections and the initial number of connections, this can increase the speed to a certain extent. 3. transaction
I want to perform an operation (update or insert) in step A. After several steps, if I find that A condition is true, I will submit all the operations. If it fails, I will roll back. Does kettle provide such transactional operations?
Kettle does not have the so-called transaction concept, and each step manages its own connection. At the beginning of this step, open the database connection and close the database connection at the end, one step is certainly not cross-session (sessions in the database). In addition, since kettle is executed in parallel, it is impossible to open a database connection for a long time, this may cause a lock. Although it is not necessarily a deadlock, it still has a significant impact on performance. Transactions in ETL have a great impact on performance, so we should not design an ETL execution sequence of dependency and transaction methods. After all, this is not OLTP, because the amount of data you need to submit at a time is several hundred GB, it is possible that the performance of any database to maintain a rollback segment of several hundred GB will not be greatly reduced. 4. I really need transaction but don't want a complicated design. Can I provide a simple way?
Kettle will launch a new feature in version 3.0.2GA. In a table output step, there is a Miscellaneous tab with a Use unique connections option, if you select this option, you can get a simple version of transaction,
Because a single database connection is used, transactions can be rolled back in case of errors. However, it should be noted that this method is based on the premise of sacrificing very high performance, this method is not suitable for large amounts of data (this method is not recommended for individuals) 5. How to Use the temporary table
I want to create an intermediate table in the ETL process. When a condition is set up, I want to convert the data in the intermediate table, when another condition is set, I want to perform another operation on the intermediate table. I want to use the temporary table of the database, and what steps should I use.
The first step is the life cycle of the temp table. The temp table is divided into a temporary transaction table and a temporary session table. As mentioned above, kettle has no concept of a transaction, therefore, there is no temporary transaction table. Each step of kettle manages its own data database connection. When the connection ends, kettle naturally loses the session handler of this connection. There is no way to retrieve the session handler in other steps, therefore, the so-called session temporary table cannot be used. When you try to open another connection, you can connect to this temporary table, however, the data in the temporary table you want is already empty (the data may not be cleared, but you cannot connect to it), so do not design a conversion using a temporary table.
The use of temporary tables is similar to the need for a "transaction" feature. It is intended to provide a buffer during the ETL process. A temporary table is often not an image of all the data in a source table. In many cases, a temporary table is a small part of the result set and may have gone through some computing process, you need temporary tables based on the following three features:
1. The table structure is fixed. A fixed table is used to accept part of the data.
2. There is no data in each connection. You want it to accept the data but not save it. It seems that the truncate table operation is executed every time.
3. Use the same name for connecting temporary tables in different cases. If you do not want to use multiple connections, use names similar to temp1, temp2, temp3, and temp4. They should have the same table structure.
Since temporary tables cannot be used, how should we design the ETL process? (A strange operation can be used to create a temporary table, but this is not recommended)
For example, if your ETL process is more single-threaded, that is, you know that there is only one such table at a time, you can create a common table, execute the truncate operation each time you connect, whether through the truncate table option of table output or by manually executing the truncate table SQL statement (in the Execute SQL script step) can achieve the purpose (based on the above 1, 2 features)
If your ETL operations are multi-threaded, multiple tables with the same structure and empty tables can be required at the same time (based on the above, 3 features ), you can create a "string + sequence" mode. You can create such a table every time you need it, and delete it after you use it, because you do not necessarily know how many tables of this type you need, deleting is better than truncate.
The following example shows how to create a table:
You can use a specified table name, such as department_temp, as the temporary table of the department. Or
Upload the argument to the table name and use the Department _ $ {argument} syntax,
If you need multiple such tables, use a sequence operation + Execute SQL script operation. Execute SQL script is in the following mode:
Create Table _? (..............)
Add parameters to the table name. A sequence or similar input operation is accepted before.
Note that the name of this parameter table includes database table input or execute SQL script. If the parameter is used as the table name, the preceding input cannot be from the database, this preparedstatement statement cannot be executed. The operation after the value from the database is "value operation" instead of string replacement. Only the argument or sequence operation is used as a parameter to replace the string. (This is also mentioned in the official FAQ) 6. Differences between update table and execute SQL script
It is slow to execute the update table operation. It compares data one by one based on the compare key and then decides whether to execute the update SQL statement, if you know how to update data, use execute SQL script operations as much as possible, and manually update SQL in it (note where the source database and target database are ), this multi-row execution method (update SQL) is certainly much faster than the single-row execution method (update table operation.
Another difference is that the execute SQL script operation can accept parameter input. It can be an SQL statement in front of a table that is completely irrelevant to it:
Select field1, field2 field3 from tableA
Follow the steps below to update another table:
Update tableB set field4 =? Where field5 =? And field6 =?
Select the execute for each row of the execute SQL script. Note that the parameters correspond one to one. (field4 corresponds to field1 values,
Field5 corresponds to the value of field2, and field6 corresponds to the value of field3) 7. kettle Performance
Kettle's performance is definitely able to cope with large-scale applications. Generally, it is based on a record with an average length of 150. Assume that the source database, both the target database and kettle are on several machines (the most common desktop working mode, dual-core, 1 GB memory), and the speed is about 5000 rows per second, if the hardware is improved, the performance can be improved, but the ETL process may inevitably encounter performance problems. The following general steps may help you.
Try to use the database connection pool
Try to increase the commit size of the batch processing
Use cache as much as possible, and cache as much as possible (mainly text files and data streams)
Kettle is made in Java. Try to use a larger memory parameter to start kettle.
You can use SQL to do some operations.
Group, merge, stream lookup, and split field operations are slow. You can try to avoid them. You can use SQL
Try to delete the index when inserting a large amount of data.
Try to avoid using update and delete operations, especially update. If you can change update to delete first, then insert.
When you can use truncate table, do not use SQL statements like delete all row.
Reasonable Partition
If the delete operation is based on a partition, do not use the Delete row method (whether it is the delete SQL or delete step) to directly drop the partition and recreate it.
Reduce the size of the input dataset as much as possible (incremental update is also for this purpose)
Try to use the database native method to load text files (Oracle sqlloader, MySQL bulk loader steps)
Do not use kettle's calculate calculation steps as much as possible. If you can use the SQL statement of the database itself, use SQL. If you cannot use SQL, try to use procedure.
You need to know where your performance bottleneck is. Sometimes you use an inappropriate method, leading to slow down the entire operation, observe how kettle log is generated to find out the slowest ETL operation.
The remote database uses file + FTP to upload data. files must be compressed. (Remote connection can be considered as long as it is not a LAN) 8. Describe the physical environment
The operating system and hardware environment of the source database, whether it is a single data source or multiple data sources, how the database is distributed, where the ETL machine is stored, and what the operating system and hardware environment are, what is the database of the target data warehouse, the operating system, the hardware environment, how to select the character set of the database, what is the data transmission method, and the development environment, what is the difference between the test environment and the actual production environment? Is an intermediate database required? What is the database version number of the source database and the version number of the test database, what is the real version number of the target database ....... This information may be scattered, but a special document is required to describe the information, whether it is when you encounter a problem and need help from others, we still find that the version numbers of the test environment are inconsistent with those of the target database. This special document provides some basic information. 9. procedure
Why can't I trigger procedure?
As mentioned in the official FAQ, both procedure and http client require a condition similar to the trigger. You can use the generate row step to generate an empty row, then link this record to the procedure step, which will trigger this procedure (if you plan to use an unconditional one-time trigger ), of course, procedure can also pass parameters as in the table input step and execute them multiple times.
Another suggestion is not to use complicated procedure to complete tasks that should have been completed by ETL tasks, such as creating tables, filling data, and creating materialized views. |
