hadoop jira, hadoop command, hadoop icon, hadoop with python, ubuntu install hadoop, hadoop in the cloud, hadoop fs, hadoop hdfs commands, hadoop data, hadoop capacity scheduler
1. Where & why is Sqoop is used? Explain with syntax
Ans: Sqoop is used for importing data from any rdbms using jdbc connector to hdfs or export an HDFS directory to a database table
Can I directly import data to Hive tables using Sqoop?
Ans: Yes
2. How do you perform a incremental data load?
Ans: By importing data using sqoop command or loading data to hive without overwrite.
--check-column (col) Specifies the column to be examined when determining which rows to import.
--incremental
(mode) Specifies how Sqoop determines which rows are new. Legal values for mode include append and lastmodified.
--last-value
(value) Specifies the maximum value of the check column from the previous import.
Sqoop supports two types of incremental imports: append and lastmodified. You can use the --incremental argument to specify the type of incremental import to perform.
You should specify append mode when importing a table where new rows are continually being added with increasing row id values. You specify the column containing the row’s id with --check-column. Sqoop imports rows where the check column has a value greater than the one specified with --last-value.
3. Can you do incremental import using sqoop? – didn’t answer the exact syntax but, told the underlying concept . answered above
4. You have a data in HDFS sytem, if you want to put some more data to into the same table, will it append the data or overwrite?
Ans:No it can’t overwrite, one way to do is copy the new file in HDFS.
Then concatenate files to a new file as shown below.
for multiple folders use:
for i in `hadoop fs -ls my-job-folder | cut -d' ' -f19` ;do `hadoop fs -cat $i/* |hadoop fs -put - $i/output.csv`; done
A table has 1000 records, but only 100 records are needed. How to achieve that?
Ans: Assign sequence number to each row and select sequence id < 101.
Else for random 100 records select top function or sample 100
1. There are some duplicate records in a table. How do you delete those duplicate rows(without Java)
Ans: using rowid by selecting min or max row id of duplicate rows.
Delete from table1 where (col1,rowid) in (sel col1,min(rowid),count(rowid) from table1 group by col1 having count(rowid) > 1)
Or
Select distinct rows from the table and put it to a temporary table.
Drop the original table and rename the temporary table to original table.
2. How to fetch the top 5th row from a table containing 100 records?
Ans: Using top function or using qualify row_number() over (partition by order by) <=6,
or using rank() function.
3. A table contains various departments, employees & their salaries. How do you fetch the maximum salary of each department and the salary must be greater than 5000
Ans: select dept,max(sal) from department group by dept having max(sal) > 5000.
hadoop jira, hadoop command, hadoop icon, hadoop with python, ubuntu install hadoop, hadoop in the cloud, hadoop fs, hadoop hdfs commands, hadoop data, hadoop capacity scheduler
This tutorial is all about on how to establish connectivity between HDFS and Teradata using Sqoop teradata connector
Installing Cloudera Connector for Teradata:
To import/export data form Hdfs to teradata using Sqoop(Assuming Sqoop is installed)
1)Download sqoop-td-connector-1.0.5.jar and put it in /user/lib/sqoop/lib
2)To ensure that Cloudera connector for Teradata can be used, copy the Teradata JDBC drivers (terajdbc4.jar and tdgssconfig.jar) to the lib directory of Sqoop installation. Without these drivers, the connector will not function correctly.
3)Create a text file called connectors in a directory named managers.d within the configuration directory of Sqoop. Create the directory managers.d if it does not exist.
4)The connectors file must have the connector class name followed by the complete path to the directory where the connector jar is located.
for Example
com.cloudera.sqoop.manager.TeradataManagerFactory=/usr/lib/sqoop/lib/sqoop-td-connector-1.0.5.jar
The TeradataManagerFactory acts as a single point of delegation for invoking the connector bundled with this distribution. An alternate way to specify TeradataManagerFactory is to add the following inside a sqoop-site.xml file, which must be inside a classpath directory:
Now you can use this connector by invoking the Sqoop tools with the appropriate connection string.
The connection string must be of the form jdbc:teradata://<td-host>/DATABASE=<td-
instance> where
<td-host> is the host name of the machine where Teradata server runs.
<td-instance> is the Teradata database instance name.
The connector automatically uses FastExport/FastLoad for better performance.
For example, the following command invokes the Sqoop import tool with three mappers and uses the
Cloudera connector for Teradata:
$ sqoop import --connect jdbc:teradata://localhost/DATABASE=sqooptest --username sqooptest --password xxxxx --table MY_TABLE --num-mappers 3 --escaped-by '\\' --fields-terminated-by ',' --lines-terminated-by '\n'
The following command invokes the Sqoop export tool with three mappers and uses the Cloudera
connector for Teradata:
$ sqoop export --connect jdbc:teradata://localhost/DATABASE=sqooptest --username sqooptest --password xxxxx --export-dir /user/john/MY_TABLE --table MY_TABLE_TARGET --num-mappers 3 --input-escaped-by '\\' --input-fields-terminated-by ','
