Sqoop + Postgresql: how to prevent quotes around table name

Question

I am trying to import a table from Postgresql to a Parquet file on HDFS.

Here is what I do:

sqoop import \
    --connect "jdbc:postgresql://pg.foo.net:5432/bar" \
    --username user_me --password $PASSWORD \
    --table foo.bar \
    --target-dir /user/me/bar \
    --as-parquetfile

and I get

INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "foo.bar" AS t LIMIT 1
ERROR manager.SqlManager: Error executing statement: org.postgresql.util.PSQLException: ERROR: relation "foo.bar" does not exist

SELECT t.* FROM "foo.bar" AS t LIMIT 1 does not work indeed, but SELECT t.* FROM foo.bar AS t LIMIT 1 does. So the problem is that table name is quoted. I tried supplying --table argument different ways, but with no effect.

How do I work it around?

EDIT

As the docs you linked state, there is a --schema argument. For some reason it is not mentioned in sqoop help import.

Another weird thing is that

--table bar --schema foo

still does not work, but

--table bar -- --schema foo

does.

Anyway, it works now. Thanks for linking the relevant docs section!

The docs explain it as follows: `If the argument -- is given on the command-line, then subsequent arguments are sent directly to the underlying tool. For example, the following adjusts the character set used by mysqldump:` So it seems the --schema is given to pg_dump: http://www.postgresql.org/docs/current/static/app-pgdump.html And that means you could use the rest of pg_dump controls...Maybe. — Jakub Kania, Apr 20 '15 at 14:03

score 3 · Accepted Answer · answered Apr 20 '15 at 12:53

3

The table name is bar, foo is the name of the schema. According to the docs you should do it like:

sqoop import \
    (...)
    --table bar \
    --schema foo
    (...)

answered Apr 20 '15 at 12:53

Jakub Kania

15,665
2
37
47

(upvoted) That is almost the solution, thanks! (I edited my answer with details) – Bartosz Marcinkowski Apr 20 '15 at 13:08

score 2 · Answer 2 · answered Apr 20 '15 at 12:54

2

According to the documentation you need to specify the schema separately:

sqoop import \
    --connect "jdbc:postgresql://pg.foo.net:5432/bar" \
    --username user_me --password $PASSWORD \
    --table bar \
    --schema foo \
    --target-dir /user/me/bar \
    --as-parquetfile

answered Apr 20 '15 at 12:54

mlinth

2,968
6
30
30

(upvoted) That is almost the solution, thanks! (I edited my answer with details) – Bartosz Marcinkowski Apr 20 '15 at 13:08
It seems that @Jakub answered a few seconds before you, so I'll accept his answer. – Bartosz Marcinkowski Apr 20 '15 at 13:14

Sqoop + Postgresql: how to prevent quotes around table name

EDIT

2 Answers2