Data Engineering

Introduction Sqoop is a tool designed to transfer data between Hadoop and relational database servers. Sqoop ships with a help tool. To display a list of all available tools, type the follow command: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 $ sqoop help usage: sqoop COMMAND [ARGS] Available commands: codegen Generate code to interact with database records create-hive-table Import a table definition into Hive eval Evaluate a SQL statement and display the results export Export an HDFS directory to a database table help List available commands import Import a table from a database to HDFS import-all-tables Import tables from a database to HDFS list-databases List available databases on a server list-tables List available tables in a database version Display version information See 'sqoop help COMMAND' for information on a specific command....

Full Table Scan When a DBMS sees a query of the form like 1 2 3 SELECT * FROM R WHERE <condition> The obvious thing to do is read through the tuples of R and report these tuples that satisfy the condition. This is called a Full Table Scan. Selective Query If we have to report 80% of the tuples in R, it makes sense to do a full table scan....

Show Replication Factor of a File 1 hadoop fs -stat %r <YOUR_FILE_BLOCK> Update Replication Factor of a File 1 hadoop fs -setrep -R -w 3 <YOUR_FILE_BLOCK>

Try not to use SELECT * and provide specific fields Bad Example 1 SELECT * FROM user; Good Example 1 SELECT id, username, tel FROM user; Reason Specify fields will save computational resources, and reduce network expense. It’s possible using pre-built table index to reduce table returns, and improve query efficiency. Avoid using OR in the where clause to connect conditions Bad Example 1 2 SELECT id, username, tel FROM user WHERE id=1 OR salary=500; Good Example Use UNION ALL to concatenate results 1 2 3 4 5 SELECT id, username, tel FROM user WHERE id=1 UNION ALL SELECT id, username, tel FROM user WHERE salary=500; Use two separate SQL 1 2 SELECT id, username, tel FROM user WHERE id=1 1 2 SELECT id, username, tel FROM user WHERE salary=500; Reason The use of OR may invalidate the table index, and thus will do full table scan....

Query Execution Order Most people would write their SQL queries starting from SELECT part, because it’s more intuitive and close to our natural language. But actually that’s not the way that SQL queries been executed in query engine. Below is the execution order of a SQL query: FROM, JOIN. Tables are joined to get the base data. WHERE. The base data is filtered. GROUP BY. The filtered based data is grouped....