大数据分析全英文 (9).pdf

《大数据分析全英文 (9).pdf》由会员分享，可在线阅读，更多相关《大数据分析全英文 (9).pdf（13页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Chap2-2 Internal Data Acquisition2Internal data acquisition toolETL(Extract,Transform,load)Data extraction,data conversion and processing,data loading;Purpose:Integrate enterprise data for analysis and processingDataMiningReportDataScienceDataWarehouseoffloadloadloadloadTransformExtractWebsite traff

2、icSocial mediaSensorlogs3Data Extraction classification1#2*3%1#2*3%41#2*3%45+1stDay2ndDay3rdDay1#2*3%1#2*3%41#2*3%45+1#2*3%1#2*3%41#2*3%45+1stDay2ndDay3rdDay1#2*3%45+2 extraction3 extraction1 extractionFull extraction Incremental Extraction1 Extract all data in the entire DB and Convert it into a fo

3、rmat that can be recognized by the ETL tool;2 It is intuitive and simple,but rarely used3 Data increase in real time,full extraction produces a large amount of redundant data,and reduces extraction efficiency;1 Only extract data newly added or modified in DB since the last extraction;2 At the same t

4、ime,it should not cause too much pressure on the existing business system;4Incremental extraction Extract new or modified data in the database since the last extraction At the same time,it cannot have a big impact on the business system How to accurately capture changing data1.Log comparison2.Timest

5、amp3 Trigger4 Full table comparisonMethods of capturing incremental data5Data Incremental Extraction-1 log comparison Evaluate the changed data through the DBs own log Oracle component CDC(changed data capture)can identify data that has changed since the last extraction;Use CDC to extract data while

6、 inserting,updating or deleting the source table,and the changed data is stored in the change table of the DB6Data incremental extraction-2 Timestamp Add and modify the timestamp field value while updating the corresponding record data.Comparing the value of the system time and the timestampTimestam

7、p7Data incremental extraction-3 TriggersCreate a trigger on the data table(for example,you can create three triggers for insert,modify and delete respectively).Whenever the source table data changes,the changed data is written to the temporary table through the corresponding trigger.The extraction t

8、hread extracts data from the temporary table.The extracted data should be marked or deleted.Insert triggermodify triggerDelete triggerTemporary table Extraction Thread Extract8Data incremental extraction-4 full table comparisonPKfield1field2。Field mOriginal tableMD5 temporary tableMD5PKfield1field2。

9、Field m*#*￥%*9Data extraction-other data sourcesIn addition to relational databases,the data sources extracted by ETL could be files,such as TXT,EXCEL files,XML files,etc.The extraction method of the file is generally full extraction,save the time stamp of the file or calculate the MD5 check code of

10、 the file before each extraction,and compare it during the next extraction.If the same,ignore this extraction10Data transforming and processingTransform components in ETL engineField mappingData filteringData cleaningData replacementData calculationData verificationData encryption and decryptionData

11、 consolidationData splitidname1name2Order($)1TomJackson1380Sum idname1name2Order(pound)1JacksonTom19011Data LoadingData loadingDirectly use SQL statements to insert,update and delete dataUse bulk loading methods,such as BCP(Bulk Copy Program),relational DB-specific bulk reprint tools or APIKettle（Th

12、e most famous open source ETL tool）12Summary of internal data acquisition methodsInternal data acquisition method:ETLE:Full extraction Incremental extraction:1.Log comparison2.time stamp3.Trigger4.full table comparison（MD5 check code）T:mapping,filtering,clarification,replacement,calculation,verification,encryption and decryption,merging,splittingL:SQL statement loading;batch loading tool;APIHaiying CheInstitute of Data Science and knowledge EngineeringSchool of Computer ScienceBeijing Institute of TechnologyThank you！