Sqoop, mail # user - Re: import SQLSERVER to Hive

import SQLSERVER to Hive

prabhu k 2012-07-17, 16:26

Hi Users,

When i am trying to import sqlserver database table to hive, getting
following error.

command:
=============
$bin/sqoop import --driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
--connect 'jdbc:sqlserver://localhost:1433;database=abc;username=sa'
--hive-overwrite --direct --table slide4_test --hive-import
--create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
--lines-terminated-by '\n' --append
Error:
=====
sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop import --driver
'com.microsoft.sqlserver.jdbc.SQLServerDriver' --connect
'jdbc:sqlserver://localhost:1433;database=abc;username=sa' --hive-overwrite
--direct --table slide4_test --hive-import --create-hive-table
--hive-table slide4_test --fields-terminated-by '\t' --lines-terminated-by
'\n' --append

Warning: /usr/lib/hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: $HADOOP_HOME is deprecated.

12/07/17 16:56:45 INFO manager.SqlManager: Using default fetchSize of 1000

12/07/17 16:56:45 INFO tool.CodeGenTool: Beginning code generation

12/07/17 16:56:59 ERROR manager.SqlManager: Error executing statement:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
the host localhost, port 1433 has failed. Error: "Connection refused.
Verify the connection properties. Make sure that an instance of SQL Server
is running on the host and accepting TCP/IP connections at the port. Make
sure that TCP connections to the port are not blocked by a firewall.".

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
the host localhost, port 1433 has failed. Error: "Connection refused.
Verify the connection properties. Make sure that an instance of SQL Server
is running on the host and accepting TCP/IP connections at the port. Make
sure that TCP connections to the port are not blocked by a firewall.".

Here, i have one question

1. How can i test sqlserver is up and running on sqoop import command
executing machine.

Please suggest and help me.

Thanks,
Prabhu.

Re: import SQLSERVER to Hive

Chalcy 2012-07-17, 18:49

Hi,

This is one of the very frustrating errors I got about 10 months ago when I
set up swoop first.

Try just list databases, if this works, then everything will work. I am
assuming that you could connect to the instance thru' your sqlserver
manager. I would make sure of that first.

sqoop list-databases --connect
"jdbc:sqlserver://instance:port;username=userid;password=mypassword"

Also port 1433, make sure it is open. You will know that, by using the
following command,
nmap -p 1433 -sT localhost

Hope this helps,
Chalcy
On Tue, Jul 17, 2012 at 12:26 PM, prabhu k <[EMAIL PROTECTED]> wrote:

> Hi Users,
>
> When i am trying to import sqlserver database table to hive, getting
> following error.
>
> command:
> =============>
> $bin/sqoop import --driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
> --connect 'jdbc:sqlserver://localhost:1433;database=abc;username=sa'
> --hive-overwrite --direct --table slide4_test --hive-import
> --create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
> --lines-terminated-by '\n' --append
>
>
> Error:
> =====>
> sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop import --driver
> 'com.microsoft.sqlserver.jdbc.SQLServerDriver' --connect
> 'jdbc:sqlserver://localhost:1433;database=abc;username=sa' --hive-overwrite
> --direct --table slide4_test --hive-import --create-hive-table
> --hive-table slide4_test --fields-terminated-by '\t' --lines-terminated-by
> '\n' --append
>
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
>
> Please set $HBASE_HOME to the root of your HBase installation.
>
> Warning: $HADOOP_HOME is deprecated.
>
>
>
> 12/07/17 16:56:45 INFO manager.SqlManager: Using default fetchSize of 1000
>
> 12/07/17 16:56:45 INFO tool.CodeGenTool: Beginning code generation
>
> 12/07/17 16:56:59 ERROR manager.SqlManager: Error executing statement:
> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> the host localhost, port 1433 has failed. Error: "Connection refused.
> Verify the connection properties. Make sure that an instance of SQL Server
> is running on the host and accepting TCP/IP connections at the port. Make
> sure that TCP connections to the port are not blocked by a firewall.".
>
> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> the host localhost, port 1433 has failed. Error: "Connection refused.
> Verify the connection properties. Make sure that an instance of SQL Server
> is running on the host and accepting TCP/IP connections at the port. Make
> sure that TCP connections to the port are not blocked by a firewall.".
>
> Here, i have one question
>
> 1. How can i test sqlserver is up and running on sqoop import command
> executing machine.
>
> Please suggest and help me.
>
> Thanks,
> Prabhu.
>
>
>

Re: import SQLSERVER to Hive

prabhu k 2012-07-18, 08:11

Thanks for the reply.

I have run the below command as per your suggestion.

bin/sqoop list-databases --connect
"jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"

I am getting following error.

sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
"jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: $HADOOP_HOME is deprecated.
12/07/18 13:33:00 INFO manager.SqlManager: Using default fetchSize of 1000
12/07/18 13:33:15 ERROR manager.CatalogQueryManager: Failed to list
databases
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
the host MT-TRNGSQL/SQLEXPRESS, port 1433 has failed. Error: "null. Verify
the connection properties. Make sure that an instance of SQL Server is
running on the host and accepting TCP/IP connections at the port. Make sure
that TCP connections to the port are not blocked by a firewall.".

and verified the port 1433 is open.

Please suggest and help me.

Thanks,
Prabhu.
On Wed, Jul 18, 2012 at 12:19 AM, Chalcy <[EMAIL PROTECTED]> wrote:

> Hi,
>
> This is one of the very frustrating errors I got about 10 months ago when
> I set up swoop first.
>
> Try just list databases, if this works, then everything will work. I am
> assuming that you could connect to the instance thru' your sqlserver
> manager. I would make sure of that first.
>
> sqoop list-databases --connect
> "jdbc:sqlserver://instance:port;username=userid;password=mypassword"
>
> Also port 1433, make sure it is open. You will know that, by using the
> following command,
> nmap -p 1433 -sT localhost
>
> Hope this helps,
> Chalcy
>
>
> On Tue, Jul 17, 2012 at 12:26 PM, prabhu k <[EMAIL PROTECTED]> wrote:
>
>> Hi Users,
>>
>> When i am trying to import sqlserver database table to hive, getting
>> following error.
>>
>> command:
>> =============>>
>> $bin/sqoop import --driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
>> --connect 'jdbc:sqlserver://localhost:1433;database=abc;username=sa'
>> --hive-overwrite --direct --table slide4_test --hive-import
>> --create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
>> --lines-terminated-by '\n' --append
>>
>>
>> Error:
>> =====>>
>> sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop import --driver
>> 'com.microsoft.sqlserver.jdbc.SQLServerDriver' --connect
>> 'jdbc:sqlserver://localhost:1433;database=abc;username=sa' --hive-overwrite
>> --direct --table slide4_test --hive-import --create-hive-table
>> --hive-table slide4_test --fields-terminated-by '\t' --lines-terminated-by
>> '\n' --append
>>
>> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
>>
>> Please set $HBASE_HOME to the root of your HBase installation.
>>
>> Warning: $HADOOP_HOME is deprecated.
>>
>>
>>
>> 12/07/17 16:56:45 INFO manager.SqlManager: Using default fetchSize of 1000
>>
>> 12/07/17 16:56:45 INFO tool.CodeGenTool: Beginning code generation
>>
>> 12/07/17 16:56:59 ERROR manager.SqlManager: Error executing statement:
>> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
>> the host localhost, port 1433 has failed. Error: "Connection refused.
>> Verify the connection properties. Make sure that an instance of SQL Server
>> is running on the host and accepting TCP/IP connections at the port. Make
>> sure that TCP connections to the port are not blocked by a firewall.".
>>
>> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
>> the host localhost, port 1433 has failed. Error: "Connection refused.
>> Verify the connection properties. Make sure that an instance of SQL Server
>> is running on the host and accepting TCP/IP connections at the port. Make
>> sure that TCP connections to the port are not blocked by a firewall.".
>>
>> Here, i have one question
>>
>> 1. How can i test sqlserver is up and running on sqoop import command
>> executing machine.

Re: import SQLSERVER to Hive

Jarek Jarcec Cecho 2012-07-18, 08:21

Hi Prabhu,
I believe that your connection URL is not valid. Please consider following Microsoft guide:

http://msdn.microsoft.com/en-us/library/ms378428(v=sql.90).aspx

The correct form seems to be:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

I believe that you used normal slash between server name and instance name instead of the backslash, right? You can specify the instance name in the property as instanceName=instance1 if you do not want to fight with proper escaping of the backslash.

Are you also able to ping MT-TRNGSQL from the box where you're running sqoop?

Jarcec

On Wed, Jul 18, 2012 at 01:41:54PM +0530, prabhu k wrote:
> Thanks for the reply.
>
> I have run the below command as per your suggestion.
>
> bin/sqoop list-databases --connect
> "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
>
> I am getting following error.
>
> sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
> "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> Please set $HBASE_HOME to the root of your HBase installation.
> Warning: $HADOOP_HOME is deprecated.
> 12/07/18 13:33:00 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/07/18 13:33:15 ERROR manager.CatalogQueryManager: Failed to list
> databases
> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> the host MT-TRNGSQL/SQLEXPRESS, port 1433 has failed. Error: "null. Verify
> the connection properties. Make sure that an instance of SQL Server is
> running on the host and accepting TCP/IP connections at the port. Make sure
> that TCP connections to the port are not blocked by a firewall.".
>
> and verified the port 1433 is open.
>
> Please suggest and help me.
>
> Thanks,
> Prabhu.
> On Wed, Jul 18, 2012 at 12:19 AM, Chalcy <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> >
> > This is one of the very frustrating errors I got about 10 months ago when
> > I set up swoop first.
> >
> > Try just list databases, if this works, then everything will work. I am
> > assuming that you could connect to the instance thru' your sqlserver
> > manager. I would make sure of that first.
> >
> > sqoop list-databases --connect
> > "jdbc:sqlserver://instance:port;username=userid;password=mypassword"
> >
> > Also port 1433, make sure it is open. You will know that, by using the
> > following command,
> > nmap -p 1433 -sT localhost
> >
> > Hope this helps,
> > Chalcy
> >
> >
> > On Tue, Jul 17, 2012 at 12:26 PM, prabhu k <[EMAIL PROTECTED]> wrote:
> >
> >> Hi Users,
> >>
> >> When i am trying to import sqlserver database table to hive, getting
> >> following error.
> >>
> >> command:
> >> =============> >>
> >> $bin/sqoop import --driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
> >> --connect 'jdbc:sqlserver://localhost:1433;database=abc;username=sa'
> >> --hive-overwrite --direct --table slide4_test --hive-import
> >> --create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
> >> --lines-terminated-by '\n' --append
> >>
> >>
> >> Error:
> >> =====> >>
> >> sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop import --driver
> >> 'com.microsoft.sqlserver.jdbc.SQLServerDriver' --connect
> >> 'jdbc:sqlserver://localhost:1433;database=abc;username=sa' --hive-overwrite
> >> --direct --table slide4_test --hive-import --create-hive-table
> >> --hive-table slide4_test --fields-terminated-by '\t' --lines-terminated-by
> >> '\n' --append
> >>
> >> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> >>
> >> Please set $HBASE_HOME to the root of your HBase installation.
> >>
> >> Warning: $HADOOP_HOME is deprecated.
> >>
> >>
> >>
> >> 12/07/17 16:56:45 INFO manager.SqlManager: Using default fetchSize of 1000
> >>
> >> 12/07/17 16:56:45 INFO tool.CodeGenTool: Beginning code generation
> >>
> >> 12/07/17 16:56:59 ERROR manager.SqlManager: Error executing statement:
> >> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to

Re: import SQLSERVER to Hive

prabhu k 2012-07-18, 12:10

Hi,

I have corrected the command like below.but still same issue.

command:

$bin/sqoop list-databases --connect
"jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
"jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: $HADOOP_HOME is deprecated.
12/07/18 17:34:22 INFO manager.SqlManager: Using default fetchSize of 1000
12/07/18 17:34:22 ERROR manager.CatalogQueryManager: Failed to list
databases
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user
'sqlserver'. ClientConnectionId:bb5ea97e-ea07-4e69-89b9-4c675fbe6d64
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

Please help me on this issue.

Thanks,
Prabhu.
On Wed, Jul 18, 2012 at 1:51 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:

> Hi Prabhu,
> I believe that your connection URL is not valid. Please consider following
> Microsoft guide:
>
> http://msdn.microsoft.com/en-us/library/ms378428(v=sql.90).aspx
>
> The correct form seems to be:
>
>
> jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
>
> I believe that you used normal slash between server name and instance name
> instead of the backslash, right? You can specify the instance name in the
> property as instanceName=instance1 if you do not want to fight with proper
> escaping of the backslash.
>
> Are you also able to ping MT-TRNGSQL from the box where you're running
> sqoop?
>
> Jarcec
>
> On Wed, Jul 18, 2012 at 01:41:54PM +0530, prabhu k wrote:
> > Thanks for the reply.
> >
> > I have run the below command as per your suggestion.
> >
> > bin/sqoop list-databases --connect
> > "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
> >
> > I am getting following error.
> >
> > sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
> > "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
> > Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> > Please set $HBASE_HOME to the root of your HBase installation.
> > Warning: $HADOOP_HOME is deprecated.
> > 12/07/18 13:33:00 INFO manager.SqlManager: Using default fetchSize of
> 1000
> > 12/07/18 13:33:15 ERROR manager.CatalogQueryManager: Failed to list
> > databases
> > com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> > the host MT-TRNGSQL/SQLEXPRESS, port 1433 has failed. Error: "null.
> Verify
> > the connection properties. Make sure that an instance of SQL Server is
> > running on the host and accepting TCP/IP connections at the port. Make
> sure
> > that TCP connections to the port are not blocked by a firewall.".
> >
> > and verified the port 1433 is open.
> >
> > Please suggest and help me.
> >
> > Thanks,
> > Prabhu.
> > On Wed, Jul 18, 2012 at 12:19 AM, Chalcy <[EMAIL PROTECTED]> wrote:
> >
> > > Hi,
> > >
> > > This is one of the very frustrating errors I got about 10 months ago
> when
> > > I set up swoop first.
> > >
> > > Try just list databases, if this works, then everything will work. I
> am
> > > assuming that you could connect to the instance thru' your sqlserver
> > > manager. I would make sure of that first.
> > >
> > > sqoop list-databases --connect
> > > "jdbc:sqlserver://instance:port;username=userid;password=mypassword"
> > >
> > > Also port 1433, make sure it is open. You will know that, by using
> the
> > > following command,
> > > nmap -p 1433 -sT localhost
> > >
> > > Hope this helps,
> > > Chalcy
> > >
> > >
> > > On Tue, Jul 17, 2012 at 12:26 PM, prabhu k <[EMAIL PROTECTED]>
> wrote:
> > >
> > >> Hi Users,
> > >>
> > >> When i am trying to import sqlserver database table to hive, getting
> > >> following error.
> > >>
> > >> command:
> > >> =============> > >>
> > >> $bin/sqoop import --driver

Re: import SQLSERVER to Hive

Jarek Jarcec Cecho 2012-07-18, 12:24

Hi Prabhu,
It seems that you've specified your credentials in the URL, however sqoop is expecting them on the command line as well. Would you mind specifying parameters --username and --password to see if it helps?

Jarcec

On Wed, Jul 18, 2012 at 05:40:57PM +0530, prabhu k wrote:
> Hi,
>
> I have corrected the command like below.but still same issue.
>
> command:
>
> $bin/sqoop list-databases --connect
> "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
>
>
> sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
> "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> Please set $HBASE_HOME to the root of your HBase installation.
> Warning: $HADOOP_HOME is deprecated.
> 12/07/18 17:34:22 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/07/18 17:34:22 ERROR manager.CatalogQueryManager: Failed to list
> databases
> com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user
> 'sqlserver'. ClientConnectionId:bb5ea97e-ea07-4e69-89b9-4c675fbe6d64
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
>
> Please help me on this issue.
>
> Thanks,
> Prabhu.
>
>
> On Wed, Jul 18, 2012 at 1:51 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:
>
> > Hi Prabhu,
> > I believe that your connection URL is not valid. Please consider following
> > Microsoft guide:
> >
> > http://msdn.microsoft.com/en-us/library/ms378428(v=sql.90).aspx
> >
> > The correct form seems to be:
> >
> >
> > jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
> >
> > I believe that you used normal slash between server name and instance name
> > instead of the backslash, right? You can specify the instance name in the
> > property as instanceName=instance1 if you do not want to fight with proper
> > escaping of the backslash.
> >
> > Are you also able to ping MT-TRNGSQL from the box where you're running
> > sqoop?
> >
> > Jarcec
> >
> > On Wed, Jul 18, 2012 at 01:41:54PM +0530, prabhu k wrote:
> > > Thanks for the reply.
> > >
> > > I have run the below command as per your suggestion.
> > >
> > > bin/sqoop list-databases --connect
> > > "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
> > >
> > > I am getting following error.
> > >
> > > sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
> > > "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
> > > Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> > > Please set $HBASE_HOME to the root of your HBase installation.
> > > Warning: $HADOOP_HOME is deprecated.
> > > 12/07/18 13:33:00 INFO manager.SqlManager: Using default fetchSize of
> > 1000
> > > 12/07/18 13:33:15 ERROR manager.CatalogQueryManager: Failed to list
> > > databases
> > > com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> > > the host MT-TRNGSQL/SQLEXPRESS, port 1433 has failed. Error: "null.
> > Verify
> > > the connection properties. Make sure that an instance of SQL Server is
> > > running on the host and accepting TCP/IP connections at the port. Make
> > sure
> > > that TCP connections to the port are not blocked by a firewall.".
> > >
> > > and verified the port 1433 is open.
> > >
> > > Please suggest and help me.
> > >
> > > Thanks,
> > > Prabhu.
> > > On Wed, Jul 18, 2012 at 12:19 AM, Chalcy <[EMAIL PROTECTED]> wrote:
> > >
> > > > Hi,
> > > >
> > > > This is one of the very frustrating errors I got about 10 months ago
> > when
> > > > I set up swoop first.
> > > >
> > > > Try just list databases, if this works, then everything will work. I
> > am
> > > > assuming that you could connect to the instance thru' your sqlserver
> > > > manager. I would make sure of that first.
> > > >
> > > > sqoop list-databases --connect
> > > > "jdbc:sqlserver://instance:port;username=userid;password=mypassword"

Re: import SQLSERVER to Hive

prabhu k 2012-07-18, 12:38

I have tried the --username and --password, but still same issue.

While i am installing SQL Server set it was in Windows Authentication mode.
How can i execute sqoop command with windows
Authentication mode or else any other alternate solution for this?

Thanks,
Prabhu.
On Wed, Jul 18, 2012 at 5:54 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:

> Hi Prabhu,
> It seems that you've specified your credentials in the URL, however sqoop
> is expecting them on the command line as well. Would you mind specifying
> parameters --username and --password to see if it helps?
>
> Jarcec
>
> On Wed, Jul 18, 2012 at 05:40:57PM +0530, prabhu k wrote:
> > Hi,
> >
> > I have corrected the command like below.but still same issue.
> >
> > command:
> >
> > $bin/sqoop list-databases --connect
> > "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
> >
> >
> > sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
> > "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
> > Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> > Please set $HBASE_HOME to the root of your HBase installation.
> > Warning: $HADOOP_HOME is deprecated.
> > 12/07/18 17:34:22 INFO manager.SqlManager: Using default fetchSize of
> 1000
> > 12/07/18 17:34:22 ERROR manager.CatalogQueryManager: Failed to list
> > databases
> > com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user
> > 'sqlserver'. ClientConnectionId:bb5ea97e-ea07-4e69-89b9-4c675fbe6d64
> > at
> >
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
> >
> > Please help me on this issue.
> >
> > Thanks,
> > Prabhu.
> >
> >
> > On Wed, Jul 18, 2012 at 1:51 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]
> >wrote:
> >
> > > Hi Prabhu,
> > > I believe that your connection URL is not valid. Please consider
> following
> > > Microsoft guide:
> > >
> > > http://msdn.microsoft.com/en-us/library/ms378428(v=sql.90).aspx
> > >
> > > The correct form seems to be:
> > >
> > >
> > >
> jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
> > >
> > > I believe that you used normal slash between server name and instance
> name
> > > instead of the backslash, right? You can specify the instance name in
> the
> > > property as instanceName=instance1 if you do not want to fight with
> proper
> > > escaping of the backslash.
> > >
> > > Are you also able to ping MT-TRNGSQL from the box where you're running
> > > sqoop?
> > >
> > > Jarcec
> > >
> > > On Wed, Jul 18, 2012 at 01:41:54PM +0530, prabhu k wrote:
> > > > Thanks for the reply.
> > > >
> > > > I have run the below command as per your suggestion.
> > > >
> > > > bin/sqoop list-databases --connect
> > > >
> "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
> > > >
> > > > I am getting following error.
> > > >
> > > > sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases
> --connect
> > > >
> "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
> > > > Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> > > > Please set $HBASE_HOME to the root of your HBase installation.
> > > > Warning: $HADOOP_HOME is deprecated.
> > > > 12/07/18 13:33:00 INFO manager.SqlManager: Using default fetchSize of
> > > 1000
> > > > 12/07/18 13:33:15 ERROR manager.CatalogQueryManager: Failed to list
> > > > databases
> > > > com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP
> connection to
> > > > the host MT-TRNGSQL/SQLEXPRESS, port 1433 has failed. Error: "null.
> > > Verify
> > > > the connection properties. Make sure that an instance of SQL Server
> is
> > > > running on the host and accepting TCP/IP connections at the port.
> Make
> > > sure
> > > > that TCP connections to the port are not blocked by a firewall.".
> > > >
> > > > and verified the port 1433 is open.
> > > >
> > > > Please suggest and help me.
> > > >
> > > > Thanks,
> > > > Prabhu.

Re: import SQLSERVER to Hive

abhijeet gaikwad 2012-07-19, 02:24

Hi Prabhu,
You may try these :
1. If your client system(from where your sqoop job is running) is not
Windows, you will have to change your authentication type on Sql Server.
2. If your client system is Windows, try integratedSecurity property in
your JDBC connection string; see -
http://msdn.microsoft.com/en-us/library/ms378988(v=sql.105).aspx

Thanks,
Abhijeet
On 18 Jul 2012 18:09, "prabhu k" <[EMAIL PROTECTED]> wrote:

> I have tried the --username and --password, but still same issue.
>
> While i am installing SQL Server set it was in Windows Authentication
> mode. How can i execute sqoop command with windows
> Authentication mode or else any other alternate solution for this?
>
> Thanks,
> Prabhu.
> On Wed, Jul 18, 2012 at 5:54 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:
>
>> Hi Prabhu,
>> It seems that you've specified your credentials in the URL, however sqoop
>> is expecting them on the command line as well. Would you mind specifying
>> parameters --username and --password to see if it helps?
>>
>> Jarcec
>>
>> On Wed, Jul 18, 2012 at 05:40:57PM +0530, prabhu k wrote:
>> > Hi,
>> >
>> > I have corrected the command like below.but still same issue.
>> >
>> > command:
>> >
>> > $bin/sqoop list-databases --connect
>> > "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
>> >
>> >
>> > sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
>> > "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
>> > Warning: /usr/lib/hbase does not exist! HBase imports will fail.
>> > Please set $HBASE_HOME to the root of your HBase installation.
>> > Warning: $HADOOP_HOME is deprecated.
>> > 12/07/18 17:34:22 INFO manager.SqlManager: Using default fetchSize of
>> 1000
>> > 12/07/18 17:34:22 ERROR manager.CatalogQueryManager: Failed to list
>> > databases
>> > com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user
>> > 'sqlserver'. ClientConnectionId:bb5ea97e-ea07-4e69-89b9-4c675fbe6d64
>> > at
>> >
>> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
>> >
>> > Please help me on this issue.
>> >
>> > Thanks,
>> > Prabhu.
>> >
>> >
>> > On Wed, Jul 18, 2012 at 1:51 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]
>> >wrote:
>> >
>> > > Hi Prabhu,
>> > > I believe that your connection URL is not valid. Please consider
>> following
>> > > Microsoft guide:
>> > >
>> > > http://msdn.microsoft.com/en-us/library/ms378428(v=sql.90).aspx
>> > >
>> > > The correct form seems to be:
>> > >
>> > >
>> > >
>> jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
>> > >
>> > > I believe that you used normal slash between server name and instance
>> name
>> > > instead of the backslash, right? You can specify the instance name in
>> the
>> > > property as instanceName=instance1 if you do not want to fight with
>> proper
>> > > escaping of the backslash.
>> > >
>> > > Are you also able to ping MT-TRNGSQL from the box where you're running
>> > > sqoop?
>> > >
>> > > Jarcec
>> > >
>> > > On Wed, Jul 18, 2012 at 01:41:54PM +0530, prabhu k wrote:
>> > > > Thanks for the reply.
>> > > >
>> > > > I have run the below command as per your suggestion.
>> > > >
>> > > > bin/sqoop list-databases --connect
>> > > >
>> "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
>> > > >
>> > > > I am getting following error.
>> > > >
>> > > > sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases
>> --connect
>> > > >
>> "jdbc:sqlserver://MT-TRNGSQL/SQLEXPRESS:1433;username=abc;password=xyz"
>> > > > Warning: /usr/lib/hbase does not exist! HBase imports will fail.
>> > > > Please set $HBASE_HOME to the root of your HBase installation.
>> > > > Warning: $HADOOP_HOME is deprecated.
>> > > > 12/07/18 13:33:00 INFO manager.SqlManager: Using default fetchSize
>> of
>> > > 1000
>> > > > 12/07/18 13:33:15 ERROR manager.CatalogQueryManager: Failed to list
>> > > > databases
>> > > > com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP

Re: import SQLSERVER to Hive

prabhu k 2012-07-19, 10:58

Hi

Thanks for the reply.

I am able to get the database list now by using below command.but not able
to import into the hive, I have pasted the command and error output below.

Please suggest and help me on this issue.

command:
========bin/sqoop list-databases --connect
"jdbc:sqlserver://ML-xyz:1433;username=abc;password=abc"

sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
"jdbc:sqlserver://ML-xyz:1433;username=abc;password=abc"
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: $HADOOP_HOME is deprecated.

12/07/19 15:54:17 INFO manager.SqlManager: Using default fetchSize of 1000
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
OnTrack
import into hive- command:
==============bin/sqoop import --driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
--connect "jdbc:sqlserver://ML-xyz/xyz_Dashboard --username=abc -P=abc"
--hive-overwrite --direct --table slide4_test --hive-import
--create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
--lines-terminated-by '\n' --append

sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop import --driver
'com.microsoft.sqlserver.jdbc.SQLServerDriver' --connect
"jdbc:sqlserver://ML-xyz/xyz_Dashboard --username=abc -P=abc"
--hive-overwrite --direct --table slide4_test --hive-import
--create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
--lines-terminated-by '\n' --append
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: $HADOOP_HOME is deprecated.
12/07/19 15:59:15 INFO manager.SqlManager: Using default fetchSize of 1000
12/07/19 15:59:15 INFO tool.CodeGenTool: Beginning code generation
12/07/19 15:59:30 ERROR manager.SqlManager: Error executing statement:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
the host ML-xyz/xyz_Dashboard --username=abc -P=abc, port 1433 has failed.
Error: "null. Verify the connection properties. Make sure that an instance
of SQL Server is running on the host and accepting TCP/IP connections at
the port. Make sure that TCP connections to the port are not blocked by a
firewall.".
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
the host ML-xyz/xyz_Dashboard --username=abc -P=abc, port 1433 has failed.
Error: "null. Verify the connection properties. Make sure that an instance
of SQL Server is running on the host and accepting TCP/IP connections at
the port. Make sure that TCP connections to the port are not blocked by a
firewall.".
Thanks,
Prabhu.

On Wed, Jul 18, 2012 at 5:54 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:

> Hi Prabhu,
> It seems that you've specified your credentials in the URL, however sqoop
> is expecting them on the command line as well. Would you mind specifying
> parameters --username and --password to see if it helps?
>
> Jarcec
>
> On Wed, Jul 18, 2012 at 05:40:57PM +0530, prabhu k wrote:
> > Hi,
> >
> > I have corrected the command like below.but still same issue.
> >
> > command:
> >
> > $bin/sqoop list-databases --connect
> > "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
> >
> >
> > sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
> > "jdbc:sqlserver://MT-TRNGSQL\SQLEXPRESS:1433;username=abc;password=xyz"
> > Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> > Please set $HBASE_HOME to the root of your HBase installation.
> > Warning: $HADOOP_HOME is deprecated.
> > 12/07/18 17:34:22 INFO manager.SqlManager: Using default fetchSize of
> 1000
> > 12/07/18 17:34:22 ERROR manager.CatalogQueryManager: Failed to list
> > databases
> > com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user
> > 'sqlserver'. ClientConnectionId:bb5ea97e-ea07-4e69-89b9-4c675fbe6d64
> > at
> >
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

Re: import SQLSERVER to Hive

Jarek Jarcec Cecho 2012-07-19, 11:06

Hi Prabhu,
please take attention to the error messages sqoop is printing to you. In most cases you will find description of your problem there.

For example now, sqoop is saying:

> 12/07/19 15:59:30 ERROR manager.SqlManager: Error executing statement:
> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> the host ML-xyz/xyz_Dashboard --username=abc -P=abc, port 1433 has failed.
> Error: "null. Verify the connection properties. Make sure that an instance
> of SQL Server is running on the host and accepting TCP/IP connections at
> the port. Make sure that TCP connections to the port are not blocked by a
> firewall.".

Notice that sqoop can't connect to host "ML-xyz/xyz_Dashboard --username=abc -P=abc". That definitely seems as incorrect hostname right?

Looking to your sqoop command line it seems to me that you accidentally put sqoop parameters --username and -P inside jdbc url. You probably want to substitute:

--connect "jdbc:sqlserver://ML-xyz/xyz_Dashboard --username=abc -P=abc"

with

--connect "jdbc:sqlserver://ML-xyz/xyz_Dashboard" --username=abc -P=abc

Please notice different position of the closing double quote character.

Jarcec

On Thu, Jul 19, 2012 at 04:28:33PM +0530, prabhu k wrote:
> Hi
>
> Thanks for the reply.
>
> I am able to get the database list now by using below command.but not able
> to import into the hive, I have pasted the command and error output below.
>
> Please suggest and help me on this issue.
>
> command:
> ========> bin/sqoop list-databases --connect
> "jdbc:sqlserver://ML-xyz:1433;username=abc;password=abc"
>
> sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop list-databases --connect
> "jdbc:sqlserver://ML-xyz:1433;username=abc;password=abc"
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> Please set $HBASE_HOME to the root of your HBase installation.
> Warning: $HADOOP_HOME is deprecated.
>
> 12/07/19 15:54:17 INFO manager.SqlManager: Using default fetchSize of 1000
> master
> tempdb
> model
> msdb
> ReportServer
> ReportServerTempDB
> OnTrack
>
>
> import into hive- command:
> ==============> bin/sqoop import --driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
> --connect "jdbc:sqlserver://ML-xyz/xyz_Dashboard --username=abc -P=abc"
> --hive-overwrite --direct --table slide4_test --hive-import
> --create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
> --lines-terminated-by '\n' --append
>
> sqoop-1.4.1-incubating__hadoop-0.20# bin/sqoop import --driver
> 'com.microsoft.sqlserver.jdbc.SQLServerDriver' --connect
> "jdbc:sqlserver://ML-xyz/xyz_Dashboard --username=abc -P=abc"
> --hive-overwrite --direct --table slide4_test --hive-import
> --create-hive-table --hive-table slide4_test --fields-terminated-by '\t'
> --lines-terminated-by '\n' --append
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> Please set $HBASE_HOME to the root of your HBase installation.
> Warning: $HADOOP_HOME is deprecated.
> 12/07/19 15:59:15 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/07/19 15:59:15 INFO tool.CodeGenTool: Beginning code generation
> 12/07/19 15:59:30 ERROR manager.SqlManager: Error executing statement:
> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> the host ML-xyz/xyz_Dashboard --username=abc -P=abc, port 1433 has failed.
> Error: "null. Verify the connection properties. Make sure that an instance
> of SQL Server is running on the host and accepting TCP/IP connections at
> the port. Make sure that TCP connections to the port are not blocked by a
> firewall.".
> com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to
> the host ML-xyz/xyz_Dashboard --username=abc -P=abc, port 1433 has failed.
> Error: "null. Verify the connection properties. Make sure that an instance
> of SQL Server is running on the host and accepting TCP/IP connections at
> the port. Make sure that TCP connections to the port are not blocked by a
> firewall.".
>
>
> Thanks,