hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Viral Bajaria <viral.baja...@gmail.com>
Subject Re: Hive & MS SQL Server
Date Mon, 28 Mar 2011 17:30:47 GMT
Appan,

Not sure if you meant to email me, but my setup uses MySQL for the
metastore. I ingest/export a lot of data to SqlServer.

Andy, for using SqlServer, the user's schema might be different but is it
owned by DBO ? Maybe instead of using the default msdb for CREATE DATABASE
your admins could use something custom.

I maybe totally wrong about the default msdb option stated above since i
tried a simple CREATE TABLE COLUMNS ( id int, name varhcar(100) ) on my
SqlServer and it worked. If you use sql server management studio you will
notice that COLUMNS is a keyword, I am guessing it has to do with some
server-side settings which does not allow you to use sql server keyword as
your tablename, columnname etc. etc. You can verify the same by doing CREATE
TABLE foo ( year int, day int )

Thanks,
Viral

On Mon, Mar 28, 2011 at 9:16 AM, Appan Thirumaligai <
athirumaligai@ngmoco.com> wrote:

> Andy,
>
> Here is a short term work around - If your goal is to backup data in SQL
> Server just copy the data out of hadoop and import it into SQL (DTS /
> BCP/SSIS based on which version you are in). Email Viral (I guess he is
> still active in the email list) and I'm sure he is using Hive, Hadoop & SQL
> Server.
>
> Thanks,
> Appan
>
> On Mar 25, 2011, at 1:59 AM, shared mailinglists wrote:
>
> Good Morning,
>
> Our DBA's created a new schema associated with the database and then made
> that the default schema for our hive user, unfortunately this resulted in
> the same problem in the logs…
>
>
> *“Check of existence of COLUMNS returned table type of VIEW”*
>
> * *
>
> *… *in that Hive still sees the default SQL Server COLUMNS view and
> therefore does create its own COLUMNS table.
>
>
> Is there any way we can configure Hive to use a different table name or any
> other approaches we could try ?
>
>
> Many thanks,
>
>
> Andy.
>
>
> On 24 March 2011 17:23, shared mailinglists <shared.mailinglists@gmail.com
> > wrote:
>
>> Hi Carl,
>>
>> Many thanks for your suggestions I will put these to our DBAs and see if
>> we can disable the default schema :-) Will post back soon.
>>
>> Cheers & thanks for the rapid replies guys,
>>
>> Andy.
>>
>>
>> On 24 March 2011 17:12, Carl Steinbach <carl@cloudera.com> wrote:
>>
>>> Hi Andy,
>>>
>>> From what I understand SQLServer has the notion of a "default schema"
>>> (usually dbo) which is used to resolve identifiers that are not defined in a
>>> user's current schema. I think you need to either undefine the default
>>> schema for your metastore user account, or else make it point to the
>>> metastore schema.
>>>
>>> Here are some relevant links with more information:
>>>
>>> http://msdn.microsoft.com/en-us/library/ms190387.aspx
>>>
>>> http://stackoverflow.com/questions/3806245/sql-server-schema-and-default-schema
>>>
>>> http://dba.fyicenter.com/faq/sql_server_2/Default_Schema_of_Your_Login_Session.html
>>>
>>> Hope this helps.
>>>
>>> Carl
>>>
>>>
>>> On Thu, Mar 24, 2011 at 9:26 AM, Edward Capriolo <edlinuxguru@gmail.com>wrote:
>>>
>>>> On Thu, Mar 24, 2011 at 11:36 AM, shared mailinglists
>>>> <shared.mailinglists@gmail.com> wrote:
>>>> > Thanks Bernie, hopefully they will.
>>>> >
>>>> > Were a small Java development team within a predominately MS
>>>> development
>>>> > house. We’re hopefully introducing new ideas but the normal company
>>>> politics
>>>> > dictate that we should use SQL Server. That way maintenance, backup,
>>>> recover
>>>> > etc etc can be handed over to the internal MS db team while freeing
us
>>>> guys
>>>> > to concentrate on better things like Hadoop & Hive :-) I assumed
with
>>>> the DB
>>>> > just being a metadata store that the database wouldn’t be an issue
but
>>>> were
>>>> > struggling a bit:-(
>>>> >
>>>> > On 24 March 2011 15:23, Bennie Schut <bschut@ebuddy.com> wrote:
>>>> >>
>>>> >> Sorry to become a bit offtopic but how do you get into a situation
>>>> where
>>>> >> sqlserver 2005 becomes a requirement for a hive internal meta store?
>>>> >>
>>>> >> I doubt many of the developers of hive will have access to this
>>>> database
>>>> >> so I don't expect a lot of response on this. But hopefully someone
>>>> can prove
>>>> >> me wrong :)
>>>> >>
>>>> >> Bennie.
>>>> >>
>>>> >>
>>>> >> On 03/24/2011 04:01 PM, shared mailinglists wrote:
>>>> >>>
>>>> >>> Hi Hive users :-)
>>>> >>>
>>>> >>> Does anybody have experience of using Hive with MS SQL Server
2005?
>>>> I’m
>>>> >>> currently stumped with the following issue
>>>> >>> https://issues.apache.org/jira/browse/HIVE-1391 where Hive (or
>>>> DataNucleus?)
>>>> >>> confuses the COLUMNS table it requires internally with that
of the
>>>> default
>>>> >>> SQL Server sys.COLUMNS or information_schema.COLUMNS View and
>>>> therefore does
>>>> >>> not automatically create the required metadata table when running
>>>> the Hive
>>>> >>> CLI.
>>>> >>>
>>>> >>>
>>>> >>> Has anybody managed to get Hive to work with SQLServer 2005
or know
>>>> how I
>>>> >>> can configure Hive to use a different table name to COLUMNS
?
>>>> Unfortunately
>>>> >>> we have to use SQL Server and do not have the option to use
Derby or
>>>> MySQL
>>>> >>> etc.
>>>> >>>
>>>> >>> Many thanks,
>>>> >>>
>>>> >>>
>>>> >>> Andy.
>>>> >>>
>>>> >>
>>>> >
>>>> >
>>>>
>>>> Let us not forget that M$ SQL Server is very advanced. It has for a
>>>> long time supported many types of things that mysql just plain did
>>>> not. (Did we all forget then mysql 3.X days where we had no
>>>> Transactions or Foreign keys? :)
>>>>
>>>> There was one ticket I closed on it.
>>>> https://issues.apache.org/jira/browse/HIVE-1391
>>>>
>>>> As far as hive is concerned, m$ SQL server is JPOX/Data Nucleus
>>>> supported so it "should" work. How many deployments exist in the wild
>>>> are unknown.
>>>>
>>>
>>>
>>
>
>

Mime
View raw message