2

I am trying to execute a stored procedure using sqoop. Idea is to perform upsert to my postgres table from an HDFS file:

HDFS FILE

  URL                ,LAST_UPDATED,           LAST_SEEN,                           STATUS_CODE
http://10.230.64.213:1080/mockserver/dashboard,2020-11-08 16:28:20.655,2020-11-08 16:28:20.655,FAIL
http://109.230.64.213:1080/mockserver/dashboard,2020-11-08 16:28:20.655,2020-11-08 16:28:20.655,FAIL

Here is my sqoop export command;

 sqoop export
--connect jdbc:postgresql://myconnection 
--username username 
--password pwd 
--export-dir postGresCopy.csv 
--input-fields-terminated-by ',' 
--call proc 
--direct
-- --schema myschema 

Here is my procedure proc.

CREATE OR REPLACE PROCEDURE  merge_db(url TEXT, last_updated TIMESTAMP,last_seen TIMESTAMP,status_code TEXT) RETURNS VOID AS
    $$
    BEGIN
        LOOP
            -- first try to update the key
            UPDATE validator SET d=status_code,c=last_seen,b=last_updated WHERE a=url;
            IF found THEN
                RETURN;
            END IF;
            -- not there, so try to insert the key
            -- if someone else inserts the same key concurrently,
            -- we could get a unique-key failure
            BEGIN
                INSERT INTO validator(a,b,c,d) VALUES (url, last_updated,last_seen,status_code);
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Do nothing, and loop to try the UPDATE again.
            END;
        END LOOP;
    END;
    LANGUAGE plpgsql;

I get below error while executing the sqoop export:

Warning: /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/11/09 17:28:27 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.16.2
20/11/09 17:28:27 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
20/11/09 17:28:27 INFO manager.SqlManager: Using default fetchSize of 1000
20/11/09 17:28:27 INFO manager.PostgresqlManager: We will use schema myschema
20/11/09 17:28:27 INFO tool.CodeGenTool: Beginning code generation
20/11/09 17:28:27 ERROR tool.ExportTool: Encountered IOException running export job: 
java.io.IOException: No columns to generate for ClassWriter
    at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1663)
    at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
    at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:75)
    at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:111)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Neethu Lalitha
  • 3,031
  • 4
  • 35
  • 60

0 Answers0