From user-return-943-apmail-sqoop-user-archive=sqoop.apache.org@sqoop.apache.org Fri Feb 1 18:14:52 2013 Return-Path: X-Original-To: apmail-sqoop-user-archive@www.apache.org Delivered-To: apmail-sqoop-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id E9886E455 for ; Fri, 1 Feb 2013 18:14:51 +0000 (UTC) Received: (qmail 22656 invoked by uid 500); 1 Feb 2013 18:14:51 -0000 Delivered-To: apmail-sqoop-user-archive@sqoop.apache.org Received: (qmail 22625 invoked by uid 500); 1 Feb 2013 18:14:51 -0000 Mailing-List: contact user-help@sqoop.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@sqoop.apache.org Delivered-To: mailing list user@sqoop.apache.org Received: (qmail 22612 invoked by uid 99); 1 Feb 2013 18:14:51 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 01 Feb 2013 18:14:51 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of james.hogarth@gmail.com designates 209.85.219.54 as permitted sender) Received: from [209.85.219.54] (HELO mail-oa0-f54.google.com) (209.85.219.54) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 01 Feb 2013 18:14:43 +0000 Received: by mail-oa0-f54.google.com with SMTP id n12so1670760oag.13 for ; Fri, 01 Feb 2013 10:14:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:in-reply-to:references:date:message-id :subject:from:to:content-type; bh=dClx12vV8ONExjdeSM7wWoNiL2ATUg5i8Vn+m+spNlU=; b=0MbjIVxuX738j171pq6PIc4XkPgYHrCOosYGEBfQXr1wqfjmw/jBGh/MYW4W4G9VZF pijpSzAoOb1iHRMLs2s78pFYusgkbkCKyAWAJXhR4UprDc8qdbC9xGVjOjSv3Xk01PEw 67PKqtXoXrFnsm8AvcRWjnSwXeH9ljjNLzRCzyYlutc5D2LfHCUq/u7QEm7af4L1EPOm A/hlhPHpP9374v9dtw/zNqOEoVhJBP49moSrPzhb4d49ANBsugKPb2ab6ez+/BKQb+eL Dt75rFFNDp1wP0IUr7A3UMoXr+zATSEsX4Y8Sy07s8PlGG+qBueLTGtgKPZ74T/x9yw9 BH6g== MIME-Version: 1.0 X-Received: by 10.60.32.200 with SMTP id l8mr10955078oei.43.1359742462104; Fri, 01 Feb 2013 10:14:22 -0800 (PST) Received: by 10.60.172.101 with HTTP; Fri, 1 Feb 2013 10:14:21 -0800 (PST) Received: by 10.60.172.101 with HTTP; Fri, 1 Feb 2013 10:14:21 -0800 (PST) In-Reply-To: <20130201172631.GA16633@localhost> References: <20130201172631.GA16633@localhost> Date: Fri, 1 Feb 2013 18:14:21 +0000 Message-ID: Subject: Re: Importing from OracleDB From: James Hogarth To: user@sqoop.apache.org Content-Type: multipart/alternative; boundary=e89a8fb1f5b07092b704d4adb6ba X-Virus-Checked: Checked by ClamAV on apache.org --e89a8fb1f5b07092b704d4adb6ba Content-Type: text/plain; charset=ISO-8859-1 On 1 Feb 2013 17:27, "Jarek Jarcec Cecho" wrote: > > Hi James, > I'm afraid that using synonyms is not supported by Oracle driver. The problem is that Oracle driver is issuing following query to get columns for imported table: > > SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME = ? ORDER BY COLUMN_ID > > This query returns no rows for synonym and thus import fails. > > As a workaround I would recommend to try out Generic JDBC Connector that is not using this query to fetch table metadata. You can force Sqoop to use Generic JDBC Connector by passing "--driver" parameter with value oracle.jdbc.OracleDriver, e.g. > > sqoop import --connect ... --driver oracle.jdbc.OracleDriver > > Another solution that might possibly help you is to install OraOop connector, but I'm not sure whether it supports synonyms or not. > > Jarcec > Hi Jarcec Thanks for confirming that. In the end my workaround with specifying the columns capitalised ended up working nicely and isn't so bad seeing as I needed to cast the oracle type 'number' to Long/BIGINT on a couple of the columns anyway... Cheers James --e89a8fb1f5b07092b704d4adb6ba Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable


On 1 Feb 2013 17:27, "Jarek Jarcec Cecho" <jarcec@apache.org> wrote:
>
> Hi James,
> I'm afraid that using synonyms is not supported by Oracle driver. = The problem is that Oracle driver is issuing following query to get columns= for imported table:
>
> =A0 SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER =3D ? AND TABL= E_NAME =3D ? ORDER BY COLUMN_ID
>
> This query returns no rows for synonym and thus import fails.
>
> As a workaround I would recommend to try out Generic JDBC Connector th= at is not using this query to fetch table metadata. You can force Sqoop to = use Generic JDBC Connector by passing "--driver" parameter with v= alue oracle.jdbc.OracleDriver, e.g.
>
> =A0 sqoop import --connect ... --driver oracle.jdbc.OracleDriver
>
> Another solution that might possibly help you is to install OraOop con= nector, but I'm not sure whether it supports synonyms or not.
>
> Jarcec
>

Hi Jarcec

Thanks for confirming that.

In the end my workaround with specifying the columns capital= ised ended up working nicely and isn't so bad seeing as I needed to cas= t the oracle type 'number' to Long/BIGINT on a couple of the column= s anyway...

Cheers

James

--e89a8fb1f5b07092b704d4adb6ba--