First, understand the following functions
Set variable length () function char_length () replace () function max () function
1.1. Setting variable SET @ variable name = value
Set @address = ' China-Shandong province-Liaocheng-Shen County ';
Select @address
1.2, Length () function char_length () function difference
Select Length (' A '),
char_length (' a ')
, Length (' Middle '),
char_length (' Medium ')
1.3. Replace () function and length () function combination
Set @address = ' China-Shandong province-Liaocheng-Shen County ';
Select @address
, replace (@address, '-', ')
as Address_1, Length (@address)
as LEN_ADD1, Length (@ Address, '-', ')
as len_add2, Length (@address)-length (replace (@address, '-', ')) as _count
ETL Cleaning field when there is a clear separator how to determine the new data table add several split out of the field
Calculates the maximum number of characters in the Com_industry to determine that the maximum value of a few fields is increased by +1, which can be split into several fields. This table is 3 so you can split 4 industry fields--4 industry levels.
Select Max (Length (com_industry)-length (replace (com_industry, '-', ')) as _max_count from
Etl1_socom_data
1.4. Set Variable Substring_index () string intercept function usage
Set @address = ' China-Shandong province-Liaocheng-Shen County ';
Select
substring_index (@address, '-', 1) as,
Substring_index (Substring_index (@address, '-', 2), '-',-1) As Province,
Substring_index (Substring_index (@address, '-', 3), '-', -1) as City,
Substring_index (@address, '- ', -1) as District
1.5. Conditional Judgment function case when
Case when then when then else value end as field name
Select Case when 89>101 then ' is greater than ' else ' less than ' end as Betl1_socom_data
second, kettle conversion Etl1 Cleaning
The first step is to build the table in the video
The field index does not have the index algorithm proposed to enhance query efficiency with btree algorithm
2.1.kettle FileName: trans_etl1_socom_data
2.2. Including controls: Table input >>> table output
2.3. Data Flow direction: S_socom_data>>>>etl1_socom_data
Kettle Conversion 1 screenshot
2.4, table input 2.4, SQL script Initial cleaning com_district and Com_industry fields
Select A.*, case when com_district like '% industry ' or com_district like '%/com_district like '% Yukon ' then null else Com_distri CT End as Com_district1, case when com_district like '% Karma ' or com_district like '%/com_district like '% fertile ' then concat ( Com_district, '-', com_industry) Else Com_industry end as com_industry_total, replace (COM_ADDR, ' Address: ', ') as COM_ADDR1, Replace (Com_phone, ' Tel: ', ') as Com_phone1, replace (com_fax, ' Fax: ', ') as COM_FAX1, replace (Com_mobile, ' Mobile: ', ') as Com_ Mobile1, replace (com_url, ' url: ', ') as COM_URL1, replace (Com_email, ' mailbox: ', ') as COM_EMAIL1, replace (Com_contactor, ' Contact person: ', ' as Com_contactor1, replace (com_emploies_nums, ' Number of companies: ', ') as COM_EMPLOIES_NUMS1, replace (Com_reg_capital, ' Registered capital: Million ', ' as Com_reg_capital1, replace (com_type, ' economic type: ', ') as Com_type1, replace (com_product, ' Company product: ', ') as Com_ Product1, replace (Com_desc, ' Company Profile: ', ') as COM_DESC1 from S_socom_data as a
2.5, table output
Table Output Settings considerations
Precautions:
① involving crawler incremental operations do not tick the crop table option
② Data connection problem Select the database in which the table is located in the table output
③ field mapping problem make sure that the fields in the data flow correspond to the number of fields in the physical table consistent three, kettle conversion Etl2 Cleaning
First build the Table Add 4 fields demo steps in the video
The field index does not have the index algorithm proposed to enhance query efficiency with btree algorithm
Field split cleaning mainly for new com_industry generated by ETL1
3.1.kettle FileName: trans_etl2_socom_data
3.2. Including controls: Table input >>> table output
3.3. Data Flow direction: Etl1_socom_data>>>>etl2_socom_data
Precautions:
① involving crawler incremental operations do not tick the crop table option
② Data connection problem Select the database in which the table is located in the table output
③ field mapping problem ensure that the field in the data flow corresponds to the number of fields in the physical table consistent
Kettle Conversion 2 screenshot
3.4, the SQL script to split the Com_industry to complete all fields cleaning registration funds field time relationship did not perform a detailed resolution of the adjustment code can be
Select a.*, Case #行业为 ' value is set to null when length (com_industry) =0 then null #其他的取第一个-delimiter before else Substring_index (com_industry, '-', 1 End as Com_industry1, case length (com_industry)-length (replace (com_industry, '-', ') =0 then null # ' Transport, warehousing and postal services- ' This value industry 2 is also set to null when length (com_industry)-length (replace (com_industry, '-', ')) =1 and length Substring_index (com_ Industry, '-', -1) =0 then null when length (com_industry)-length (replace (com_industry, '-', ')) =1 then-Substring_index ( Com_industry, '-', -1 else Substring_index (Substring_index (com_industry, '-', 2), '-', -1) end as Com_industry2 Length (com_industry)-length (replace (com_industry, '-', ')) <=1 then null when length (com_industry)-length (replace (Com_industry, '-', ')) =2 then Substring_index (com_industry, '-', -1) Else Substring_index (Substring_index, '-', 3), '-', -1 "end as Com_industry3, Case Length (com_industry)-length (replace (com_industry, '-', ')) <=2 then null else substring_ Index (Com_industry, '-', -1) end as Com_Industry4 from Etl1_socom_data as a
Four, the cleaning effect quality inspection 4.1 crawler data source data and Web site data match
If the work itself is a crawler and data processing in the process, the crawl has actually been judged, this step can be omitted, if the docking upstream reptile colleagues, this step to judge first, otherwise cleaning is not diligent, generally require the crawler colleagues to store the requested URL for data quality 4.2 Calculation of data source of reptile and the quantity of each ETL Clean data table
Note: 3 table data should be equal in SQL script without aggregation filtering
4.2.1, SQL query The following table I am in the same database if you do not follow the same database from the database name that contains the table
Do not recommend the use of data when the volume is large
Select COUNT (1) from S_socom_data
UNION ALL
Select COUNT (1) from Etl1_socom_data
UNION ALL
Select Count (1) from Etl2_socom_data
4.2.2 The total output of the table according to the Kettle conversion after execution
Kettle Table Output Total data amount 4.3 view etl Cleaning quality
Ensure that the first two steps are correct, data processing is responsible for the ETL cleaning work self-examination start the field to clean the data source write a script check SOCOM site is mainly to the region and industry has been cleaned to other fields do replace extra field processing, so take a script check,
Find Page_url and website data for verification
Where this is written to make it easy to see how a field is cleaned
SELECT * from
etl2_socom_data
where com_district is null and length (com_industry)-length (replace (com_industry ,