The same problem also occurred when importing into HBase. Sqoop does not use the alias name in the following example,and throws a "Duplicate Column" error.
$ sqoop import --query "select id as p_id, p.name as p_name, o.name as o_name from person as p, org as o WHERE p.org_id = o.id and $CONDITIONS " --hbase-table person_and_org --column-family demographics --split-by person.id --boundary-query "select min(p.id), max(p.id) from person as p" 13/09/10 19:00:08 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 13/09/10 19:00:08 INFO tool.CodeGenTool: Beginning code generation 13/09/10 19:00:08 ERROR tool.ImportTool: Imported Failed: Duplicate Column identifier specified: 'name' The workaround is to use a function in the free format SQL. select id as p_id, concat(p.name, "") as p_name, concat(o.name, "") as o_name from person as p, org as o WHERE p.org_id = o.id and $CONDITIONS On 9/16/13 3:10 AM, "Babu, Prashanth" <[email protected]> wrote: >Thanks Jarcec. >It's the same issue even with "import" command as well. > >Regards, >Prashanth. > > >-----Original Message----- >From: Jarek Jarcec Cecho [mailto:[email protected]] >Sent: Monday, September 16, 2013 1:39 PM >To: [email protected] >Subject: Re: Issue with Sqoop column aliases > >Hi Prashanth, >I would like to mention that the "eval" tool is provided only for >evaluation purpose and should not be used in production. > >Jarcec > >On Fri, Sep 13, 2013 at 02:56:22AM +0000, Babu, Prashanth wrote: >> Hi, >> >> >> >> I came across an issue in Sqoop v1.4.4 import for column aliases. I >>remember it was present even in earlier versions [I first saw this issue >>in v1.2.0 dating back to mid-2011]. >> >> >> >> Consider a Sqoop eval: >> >> sqoop eval --connect jdbc:mysql://my_ip/my_db --username root >>--password **** --query "SELECT c.id AS cid, concat(c.firstname, >>c.lastname) AS name FROM customer c LIMIT 10" >> >> >> >> The above query gets me the data as required and but one of the column >>aliases is incorrect [instead of cid, it shows the column alias as id]. >> >> id name >> >> >> >> I have found a workaround [though it is not elegant per-se] >> >> sqoop eval --connect jdbc:mysql://my_ip/my_db --username root >>--password **** --query "SELECT concat(c.id, '') AS cid, >>concat(c.firstname, c.lastname) AS name FROM customer c LIMIT 10" >> >> >> >> The above query gets me the data as required and with the exact column >>aliases I have mentioned. >> >> cid name >> >> >> >> I am not sure if I am missing something here. >> >> Is it like if I am getting just the column, Sqoop does not consider the >>column alias. And if I give a function like concat as above, it >>considers the column alias as well? >> >> >> >> Thanks. >> >> >> >> Regards, >> >> Prashanth. >> >> ______________________________________________________________________ >> Disclaimer:This email and any attachments are sent in strictest >>confidence for the sole use of the addressee and may contain legally >>privileged, confidential, and proprietary data. If you are not the >>intended recipient, please advise the sender by replying promptly to >>this email and then delete and destroy this email and any attachments >>without any further use, copying or forwarding > >______________________________________________________________________ >Disclaimer:This email and any attachments are sent in strictest >confidence for the sole use of the addressee and may contain legally >privileged, confidential, and proprietary data. If you are not the >intended recipient, please advise the sender by replying promptly to this >email and then delete and destroy this email and any attachments without >any further use, copying or forwarding
