Query & Insert from Hive

Github Project : example-talend-query-insert-from-hive

Preamble

Configuration: Group of Context

To create the different jobs displayed in this article, you have to create a repository with saagie's platform information (value).

Query from Hive

Example: Count the number of lines.

• Create a new job
• Add the component tHiveConnection : allows the creation of a Hive connection
• Add the component tHiveInput : reads an Hive table and extracts fields based on a Hive Query
• Add the component tLogRow : displays results
• Create links:
  • tHiveConnection is connected with tHiveInput (through "OnSubjobOk")
  • tHiveInput is connected with tLogRow (through "Main")

• Double click on tHiveConnection and set its properties:
  • Add a "Cloudera" distribution and select the latest version of Cloudera
  • Enter a host
  • Enter a port
  • Enter a name of BDD
  • Enter a user
  • Enter a password

• Double click on the component tImpalaInput:
  • Tick Use an existing connection
  • Enter a table name
  • Enter your request:  SELECT COUNT(*) FROM "+context.Table_Hive+"
    Use context variables if possible: "+context.Table_Hive+"

• Run the job

Insert from Hive

Example: Create a new table and insert ten rows (since old table in Impala)

• Create a new job
• Add the component tHiveConnection: allows the creation of a Hive connection.
• Add the component tHiveRow: executes an SQL query at each of Talend flow's iterations
• Create links:
  • tHiveConnection is connected with tHiveRow (through "OnSubjobOk")

• Double click on tHiveConnection and set its properties:
  • Add a "Cloudera" distribution and select the latest version of Cloudera
  • Enter a host
  • Enter a port
  • Enter a name of BDD
  • Enter a user
  • Enter a password

• Click on the component tHiveRow:
  • Tick Use an existing connection
  • Enter a table name
  • Enter a new table name
  • Enter your request: "CREATE TABLE "+context.New_Table_Hive+" AS SELECT *  FROM "+context.Table_Hive+" LIMIT 10 "
    Use context variables if possible : "+context.Table_Hive+"

• Run the job