You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Margus Roo <ma...@roo.ee> on 2016/05/10 20:03:44 UTC
Create external table
Hi
Can someone explain or provide documentation how Hive creates external
tables?
I have problem with creating external table in case I am pointing
location in hdfs in to directory where are loads of files. Beeline just
hangs or there will be other errors.
In case I point location in to the empty directory then hive creates table.
So does hive looks into files during creating external table?
I can not find any documentation explaining it.
--
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
I have kerberos enabled in my cluster.
In case I create external table using beeline I see from hdfs namenode
log that it does Kerberos auth for every single file I guess.
It may be the reason why creating external hive table fails in case I
have loads of directories and files under them.
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 12/05/16 10:41, Margus Roo wrote:
>
> No I got closer and discovered that my problem is related with
> permissions.
>
> In example
>
> drwxr-xr-x - margusja hdfs 0 2016-05-12 03:33 /tmp/files_10k
>
> ...
>
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1959.txt
> -rw-r--r-- 3 margusja hdfs 4 2016-05-12 02:01
> /tmp/files_10k/f196.txt
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1960.txt
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1961.txt
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1962.txt
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1963.txt
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1964.txt
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1965.txt
> -rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1966.txt
>
> ...
>
> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> 0: jdbc:hive2://bigdata29.webmedia.int:10000/> create external table
> files_10k (i int) row format delimited fields terminated by '\t'
> location '/tmp/files_10k';
> No rows affected (3.184 seconds)
> 0: jdbc:hive2://bigdata29.webmedia.int:10000/>
>
>
> Now I change owner to flume in example.
>
> drwxr-xr-x - flume hdfs 0 2016-05-12 03:33 /tmp/files_10k
>
> ...
>
> -rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1968.txt
> -rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1969.txt
> -rw-r--r-- 3 flume hdfs 4 2016-05-12 02:01
> /tmp/files_10k/f197.txt
> -rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1970.txt
> -rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1971.txt
> -rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
> /tmp/files_10k/f1972.txt
>
> ...
>
> Others can read. In example user margusja can read
>
> [margusja@bigdata29 ~]$ hdfs dfs -ls /tmp/files_10k
> Found 1112 items
> -rw-r--r-- 3 flume hdfs 2 2016-05-12 01:59
> /tmp/files_10k/f1.txt
> -rw-r--r-- 3 flume hdfs 3 2016-05-12 01:59
> /tmp/files_10k/f10.txt
> -rw-r--r-- 3 flume hdfs 4 2016-05-12 01:59
> /tmp/files_10k/f100.txt
> -rw-r--r-- 3 flume hdfs 5 2016-05-12 01:59
> /tmp/files_10k/f1000.txt
> -rw-r--r-- 3 flume hdfs 6 2016-05-12 01:59
> /tmp/files_10k/f10000.txt
>
> I try now create a table
>
> 0: jdbc:hive2://bigdata29.webmedia.int:10000/> create external table
> files_10k (i int) row format delimited fields terminated by '\t'
> location '/tmp/files_10k';
> Error: Error while compiling statement: FAILED:
> HiveAccessControlException Permission denied: user [margusja] does not
> have [READ] privilege on [hdfs://mycluster/tmp/files_10k]
> (state=42000,code=40000)
> 0: jdbc:hive2://bigdata29.webmedia.int:10000/>
>
> In Hiveserver2.log:
>
> 2016-05-12 03:38:58,111 INFO [HiveServer2-Handler-Pool: Thread-69]:
> parse.ParseDriver (ParseDriver.java:parse(185)) - Parsing command:
> create external table files_10k (i int) row format delimited fields
> terminated by '\t' location '/tmp/files_10k'
> 2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
> parse.ParseDriver (ParseDriver.java:parse(209)) - Parse Completed
> 2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
> log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
> method=parse start=1463038738111 end=1463038738112 duration=1
> from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
> log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
> method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
> parse.CalcitePlanner (SemanticAnalyzer.java:analyzeInternal(10114)) -
> Starting Semantic Analysis
> 2016-05-12 03:38:58,113 INFO [HiveServer2-Handler-Pool: Thread-69]:
> parse.CalcitePlanner (SemanticAnalyzer.java:analyzeCreateTable(10776))
> - Creating table default.files_10k position=22
> 2016-05-12 03:38:58,113 INFO [HiveServer2-Handler-Pool: Thread-69]:
> metastore.HiveMetaStore (HiveMetaStore.java:logInfo(747)) - 1:
> get_database: default
> 2016-05-12 03:38:58,113 INFO [HiveServer2-Handler-Pool: Thread-69]:
> HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(372)) -
> ugi=hive/bigdata29.webmedia.int@TESTHADOOP.COM ip=unknown-ip-addr
> cmd=get_database: default
> 2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
> ql.Driver (Driver.java:compile(466)) - Semantic Analysis Completed
> 2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
> log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
> method=semanticAnalyze start=1463038738112 end=1463038738118
> duration=6 from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
> ql.Driver (Driver.java:getSchema(246)) - Returning Hive schema:
> Schema(fieldSchemas:null, properties:null)
> 2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
> log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
> method=doAuthorization from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-12 03:39:00,148 INFO
> [org.apache.hadoop.util.JvmPauseMonitor$Monitor@53bb71e5]:
> util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected pause
> in JVM or host machine (eg GC): pause of approximately 1916ms
> GC pool 'PS MarkSweep' had collection(s): count=1 time=2002ms
> 2016-05-12 03:39:01,733 INFO
> [org.apache.hadoop.util.JvmPauseMonitor$Monitor@53bb71e5]:
> util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected pause
> in JVM or host machine (eg GC): pause of approximately 1081ms
> GC pool 'PS MarkSweep' had collection(s): count=1 time=1455ms
> 2016-05-12 03:39:20,984 ERROR [HiveServer2-Handler-Pool: Thread-69]:
> authorizer.RangerHiveAuthorizer
> (RangerHiveAuthorizer.java:isURIAccessAllowed(755)) - Error getting
> permissions for hdfs://mycluster/tmp/files_10k
> java.io.IOException: Couldn't create proxy provider class
> org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider
>
>
> I am confused. What extra rights Hive except?
>
>
> Margus (margusja) Roo
> http://margus.roo.ee
> skype: margusja
> +372 51 48 780
> On 11/05/16 14:17, Margus Roo wrote:
>>
>> One more example:
>>
>> [hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /user/margusja/files_10k/
>> 1 9.8 K 47.7 K /user/margusja/files_10k
>> [hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /datasource/dealgate/
>> 53 7.9 K 8.5 G /datasource/dealgate
>>
>> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> create external table
>> files_10k (i int) row format delimited fields terminated by '\t'
>> location '/user/margusja/files_10k';
>> No rows affected (0.197 seconds)
>> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> drop table files_10k;
>> No rows affected (0.078 seconds)
>> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> create external table
>> files_10k (i int) row format delimited fields terminated by '\t'
>> location '/datasource/dealgate';
>> Error: org.apache.thrift.transport.TTransportException
>> (state=08S01,code=0)
>> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def>
>>
>>
>> So in my point of view beeline in some reason looks data and old hive
>> client does not.
>>
>> Margus (margusja) Roo
>> http://margus.roo.ee
>> skype: margusja
>> +372 51 48 780
>> On 11/05/16 13:35, Margus Roo wrote:
>>>
>>> More information:
>>>
>>> 2016-05-11 13:31:17,086 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: parse.ParseDriver (ParseDriver.java:parse(185)) -
>>> Parsing command: create external table files_10k (i int) row format
>>> delimited fields terminated by '\t' location '/user/margusja/files_10k'
>>> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: parse.ParseDriver (ParseDriver.java:parse(209)) -
>>> Parse Completed
>>> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) -
>>> </PERFLOG method=parse start=1462962677086 end=1462962677089
>>> duration=3 from=org.apache.hadoop.hive.ql.Driver>
>>> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) -
>>> <PERFLOG method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver>
>>> 2016-05-11 13:31:17,090 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: parse.CalcitePlanner
>>> (SemanticAnalyzer.java:analyzeInternal(10114)) - Starting Semantic
>>> Analysis
>>> 2016-05-11 13:31:17,093 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: parse.CalcitePlanner
>>> (SemanticAnalyzer.java:analyzeCreateTable(10776)) - Creating table
>>> default.files_10k position=22
>>> 2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: metastore.HiveMetaStore
>>> (HiveMetaStore.java:logInfo(747)) - 2: get_database: default
>>> 2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: HiveMetaStore.audit
>>> (HiveMetaStore.java:logAuditEvent(372)) -
>>> ugi=hive/hadoopnn1.estpak.ee@TESTHADOOP.COM ip=unknown-ip-addr
>>> cmd=get_database: default
>>> 2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool:
>>> Thread-5867]: security.UserGroupInformation
>>> (UserGroupInformation.java:getGroupNames(1521)) - No groups
>>> available for user hive
>>> 2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool:
>>> Thread-5867]: security.UserGroupInformation
>>> (UserGroupInformation.java:getGroupNames(1521)) - No groups
>>> available for user hive
>>> 2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool:
>>> Thread-5867]: security.UserGroupInformation
>>> (UserGroupInformation.java:getGroupNames(1521)) - No groups
>>> available for user hive
>>> 2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool:
>>> Thread-5867]: security.UserGroupInformation
>>> (UserGroupInformation.java:getGroupNames(1521)) - No groups
>>> available for user hive
>>> 2016-05-11 13:31:17,099 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: metadata.HiveUtils
>>> (HiveUtils.java:getMetaStoreAuthorizeProviderManagers(353)) - Adding
>>> metastore authorization provider:
>>> org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider
>>> 2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool:
>>> Thread-5867]: security.UserGroupInformation
>>> (UserGroupInformation.java:getGroupNames(1521)) - No groups
>>> available for user hive
>>> 2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool:
>>> Thread-5867]: security.UserGroupInformation
>>> (UserGroupInformation.java:getGroupNames(1521)) - No groups
>>> available for user hive
>>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: ql.Driver (Driver.java:compile(466)) - Semantic
>>> Analysis Completed
>>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) -
>>> </PERFLOG method=semanticAnalyze start=1462962677089
>>> end=1462962677106 duration=17 from=org.apache.hadoop.hive.ql.Driver>
>>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: ql.Driver (Driver.java:getSchema(246)) - Returning
>>> Hive schema: Schema(fieldSchemas:null, properties:null)
>>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) -
>>> <PERFLOG method=doAuthorization from=org.apache.hadoop.hive.ql.Driver>
>>> 2016-05-11 13:31:17,107 WARN [HiveServer2-Handler-Pool:
>>> Thread-5867]: security.UserGroupInformation
>>> (UserGroupInformation.java:getGroupNames(1521)) - No groups
>>> available for user margusja
>>> 2016-05-11 13:31:18,289 INFO
>>> [org.apache.hadoop.util.JvmPauseMonitor$Monitor@59f45950]:
>>> util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected
>>> pause in JVM or host machine (eg GC): pause of approximately 1092ms
>>> 2016-05-11 13:31:29,547 INFO [HiveServer2-Handler-Pool:
>>> Thread-5867]: retry.RetryInvocationHandler
>>> (RetryInvocationHandler.java:invoke(144)) - Exception while invoking
>>> getListing of class ClientNamenodeProtocolTranslatorPB over
>>> hadoopnn1.estpak.ee/88.196.164.42:8020. Trying to fail over immediately.
>>> java.io.IOException: com.google.protobuf.ServiceException:
>>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>>> at
>>> org.apache.hadoop.ipc.ProtobufHelper.getRemoteException(ProtobufHelper.java:47)
>>> at
>>> org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:580)
>>> at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
>>> at
>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>> at java.lang.reflect.Method.invoke(Method.java:497)
>>> at
>>> org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:252)
>>> at
>>> org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:104)
>>> at com.sun.proxy.$Proxy16.getListing(Unknown Source)
>>> at
>>> org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2094)
>>> at
>>> org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2077)
>>> at
>>> org.apache.hadoop.hdfs.DistributedFileSystem.listStatusInternal(DistributedFileSystem.java:832)
>>> at
>>> org.apache.hadoop.hdfs.DistributedFileSystem.access$700(DistributedFileSystem.java:106)
>>> at
>>> org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:863)
>>> at
>>> org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:859)
>>> at
>>> org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)
>>> at
>>> org.apache.hadoop.hdfs.DistributedFileSystem.listStatus(DistributedFileSystem.java:859)
>>> at
>>> org.apache.hadoop.hive.common.FileUtils.isOwnerOfFileHierarchy(FileUtils.java:481)
>>> at
>>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
>>> at
>>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
>>> at
>>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
>>> at
>>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
>>> at
>>> org.apache.hadoop.hive.ql.Driver.doAuthorizationV2(Driver.java:817)
>>> at
>>> org.apache.hadoop.hive.ql.Driver.doAuthorization(Driver.java:608)
>>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:499)
>>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:314)
>>> at
>>> org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1164)
>>> at
>>> org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1158)
>>> at
>>> org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
>>> at
>>> org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181)
>>> at
>>> org.apache.hive.service.cli.operation.Operation.run(Operation.java:257)
>>> at
>>> org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:410)
>>> at
>>> org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:397)
>>> at
>>> org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:274)
>>> at
>>> org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486)
>>> at
>>> org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313)
>>> at
>>> org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298)
>>> at
>>> org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
>>> at
>>> org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
>>> at
>>> org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:692)
>>> at
>>> org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
>>> at
>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
>>> at
>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
>>> at java.lang.Thread.run(Thread.java:745)
>>> Caused by: com.google.protobuf.ServiceException:
>>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>>> at
>>> org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:271)
>>> at com.sun.proxy.$Proxy15.getListing(Unknown Source)
>>> at
>>> org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:573)
>>> ... 39 more
>>>
>>> I have hdfs namenode high availability configured and automatic fail
>>> over enabled. I can see that active namenode does not change during
>>> the creating table process.
>>>
>>> Also I have hive high availability configured.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Margus (margusja) Roo
>>> http://margus.roo.ee
>>> skype: margusja
>>> +372 51 48 780
>>> On 11/05/16 12:26, Margus Roo wrote:
>>>>
>>>> Sadly in our environment:
>>>>
>>>>
>>>> Generated files like you did.
>>>>
>>>> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
>>>> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
>>>> Transaction isolation: TRANSACTION_REPEATABLE_READ
>>>> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado> create external
>>>> table files_10k (i int) row format delimited fields terminated by
>>>> '\t' location '/user/margusja/files_10k';
>>>> Error: Shutdown in progress, cannot remove a shutdownHook
>>>> (state=,code=0)
>>>> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado>
>>>>
>>>> Using just hive:
>>>>
>>>> [margusja@hadoopnn1 ~]$ hive
>>>> WARNING: Use "yarn jar" to launch YARN applications.
>>>> log4j:WARN No such property [maxBackupIndex] in
>>>> org.apache.log4j.DailyRollingFileAppender.
>>>>
>>>> Logging initialized using configuration in
>>>> file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
>>>> hive> create external table files_10k (i int) row format delimited
>>>> fields terminated by '\t' location '/user/margusja/files_10k';
>>>> OK
>>>> Time taken: 1.255 seconds
>>>> hive>
>>>>
>>>>
>>>> Margus (margusja) Roo
>>>> http://margus.roo.ee
>>>> skype: margusja
>>>> +372 51 48 780
>>>> On 11/05/16 10:16, Markovitz, Dudu wrote:
>>>>> create external table files_10k (i int) row format delimited
>>>>> fields terminated by '\t' location '/tmp/files_10k';
>>>>
>>>
>>
>
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
No I got closer and discovered that my problem is related with permissions.
In example
drwxr-xr-x - margusja hdfs 0 2016-05-12 03:33 /tmp/files_10k
...
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1959.txt
-rw-r--r-- 3 margusja hdfs 4 2016-05-12 02:01
/tmp/files_10k/f196.txt
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1960.txt
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1961.txt
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1962.txt
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1963.txt
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1964.txt
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1965.txt
-rw-r--r-- 3 margusja hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1966.txt
...
Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://bigdata29.webmedia.int:10000/> create external table
files_10k (i int) row format delimited fields terminated by '\t'
location '/tmp/files_10k';
No rows affected (3.184 seconds)
0: jdbc:hive2://bigdata29.webmedia.int:10000/>
Now I change owner to flume in example.
drwxr-xr-x - flume hdfs 0 2016-05-12 03:33 /tmp/files_10k
...
-rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1968.txt
-rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1969.txt
-rw-r--r-- 3 flume hdfs 4 2016-05-12 02:01
/tmp/files_10k/f197.txt
-rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1970.txt
-rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1971.txt
-rw-r--r-- 3 flume hdfs 5 2016-05-12 02:01
/tmp/files_10k/f1972.txt
...
Others can read. In example user margusja can read
[margusja@bigdata29 ~]$ hdfs dfs -ls /tmp/files_10k
Found 1112 items
-rw-r--r-- 3 flume hdfs 2 2016-05-12 01:59 /tmp/files_10k/f1.txt
-rw-r--r-- 3 flume hdfs 3 2016-05-12 01:59 /tmp/files_10k/f10.txt
-rw-r--r-- 3 flume hdfs 4 2016-05-12 01:59
/tmp/files_10k/f100.txt
-rw-r--r-- 3 flume hdfs 5 2016-05-12 01:59
/tmp/files_10k/f1000.txt
-rw-r--r-- 3 flume hdfs 6 2016-05-12 01:59
/tmp/files_10k/f10000.txt
I try now create a table
0: jdbc:hive2://bigdata29.webmedia.int:10000/> create external table
files_10k (i int) row format delimited fields terminated by '\t'
location '/tmp/files_10k';
Error: Error while compiling statement: FAILED:
HiveAccessControlException Permission denied: user [margusja] does not
have [READ] privilege on [hdfs://mycluster/tmp/files_10k]
(state=42000,code=40000)
0: jdbc:hive2://bigdata29.webmedia.int:10000/>
In Hiveserver2.log:
2016-05-12 03:38:58,111 INFO [HiveServer2-Handler-Pool: Thread-69]:
parse.ParseDriver (ParseDriver.java:parse(185)) - Parsing command:
create external table files_10k (i int) row format delimited fields
terminated by '\t' location '/tmp/files_10k'
2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
parse.ParseDriver (ParseDriver.java:parse(209)) - Parse Completed
2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
method=parse start=1463038738111 end=1463038738112 duration=1
from=org.apache.hadoop.hive.ql.Driver>
2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver>
2016-05-12 03:38:58,112 INFO [HiveServer2-Handler-Pool: Thread-69]:
parse.CalcitePlanner (SemanticAnalyzer.java:analyzeInternal(10114)) -
Starting Semantic Analysis
2016-05-12 03:38:58,113 INFO [HiveServer2-Handler-Pool: Thread-69]:
parse.CalcitePlanner (SemanticAnalyzer.java:analyzeCreateTable(10776)) -
Creating table default.files_10k position=22
2016-05-12 03:38:58,113 INFO [HiveServer2-Handler-Pool: Thread-69]:
metastore.HiveMetaStore (HiveMetaStore.java:logInfo(747)) - 1:
get_database: default
2016-05-12 03:38:58,113 INFO [HiveServer2-Handler-Pool: Thread-69]:
HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(372)) -
ugi=hive/bigdata29.webmedia.int@TESTHADOOP.COM ip=unknown-ip-addr
cmd=get_database: default
2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
ql.Driver (Driver.java:compile(466)) - Semantic Analysis Completed
2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
method=semanticAnalyze start=1463038738112 end=1463038738118 duration=6
from=org.apache.hadoop.hive.ql.Driver>
2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
ql.Driver (Driver.java:getSchema(246)) - Returning Hive schema:
Schema(fieldSchemas:null, properties:null)
2016-05-12 03:38:58,118 INFO [HiveServer2-Handler-Pool: Thread-69]:
log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
method=doAuthorization from=org.apache.hadoop.hive.ql.Driver>
2016-05-12 03:39:00,148 INFO
[org.apache.hadoop.util.JvmPauseMonitor$Monitor@53bb71e5]:
util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected pause in
JVM or host machine (eg GC): pause of approximately 1916ms
GC pool 'PS MarkSweep' had collection(s): count=1 time=2002ms
2016-05-12 03:39:01,733 INFO
[org.apache.hadoop.util.JvmPauseMonitor$Monitor@53bb71e5]:
util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected pause in
JVM or host machine (eg GC): pause of approximately 1081ms
GC pool 'PS MarkSweep' had collection(s): count=1 time=1455ms
2016-05-12 03:39:20,984 ERROR [HiveServer2-Handler-Pool: Thread-69]:
authorizer.RangerHiveAuthorizer
(RangerHiveAuthorizer.java:isURIAccessAllowed(755)) - Error getting
permissions for hdfs://mycluster/tmp/files_10k
java.io.IOException: Couldn't create proxy provider class
org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider
I am confused. What extra rights Hive except?
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 11/05/16 14:17, Margus Roo wrote:
>
> One more example:
>
> [hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /user/margusja/files_10k/
> 1 9.8 K 47.7 K /user/margusja/files_10k
> [hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /datasource/dealgate/
> 53 7.9 K 8.5 G /datasource/dealgate
>
> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> create external table
> files_10k (i int) row format delimited fields terminated by '\t'
> location '/user/margusja/files_10k';
> No rows affected (0.197 seconds)
> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> drop table files_10k;
> No rows affected (0.078 seconds)
> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> create external table
> files_10k (i int) row format delimited fields terminated by '\t'
> location '/datasource/dealgate';
> Error: org.apache.thrift.transport.TTransportException
> (state=08S01,code=0)
> 2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def>
>
>
> So in my point of view beeline in some reason looks data and old hive
> client does not.
>
> Margus (margusja) Roo
> http://margus.roo.ee
> skype: margusja
> +372 51 48 780
> On 11/05/16 13:35, Margus Roo wrote:
>>
>> More information:
>>
>> 2016-05-11 13:31:17,086 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: parse.ParseDriver (ParseDriver.java:parse(185)) -
>> Parsing command: create external table files_10k (i int) row format
>> delimited fields terminated by '\t' location '/user/margusja/files_10k'
>> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: parse.ParseDriver (ParseDriver.java:parse(209)) - Parse
>> Completed
>> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) -
>> </PERFLOG method=parse start=1462962677086 end=1462962677089
>> duration=3 from=org.apache.hadoop.hive.ql.Driver>
>> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) -
>> <PERFLOG method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver>
>> 2016-05-11 13:31:17,090 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: parse.CalcitePlanner
>> (SemanticAnalyzer.java:analyzeInternal(10114)) - Starting Semantic
>> Analysis
>> 2016-05-11 13:31:17,093 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: parse.CalcitePlanner
>> (SemanticAnalyzer.java:analyzeCreateTable(10776)) - Creating table
>> default.files_10k position=22
>> 2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: metastore.HiveMetaStore
>> (HiveMetaStore.java:logInfo(747)) - 2: get_database: default
>> 2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: HiveMetaStore.audit
>> (HiveMetaStore.java:logAuditEvent(372)) -
>> ugi=hive/hadoopnn1.estpak.ee@TESTHADOOP.COM ip=unknown-ip-addr
>> cmd=get_database: default
>> 2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool:
>> Thread-5867]: security.UserGroupInformation
>> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
>> for user hive
>> 2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool:
>> Thread-5867]: security.UserGroupInformation
>> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
>> for user hive
>> 2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool:
>> Thread-5867]: security.UserGroupInformation
>> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
>> for user hive
>> 2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool:
>> Thread-5867]: security.UserGroupInformation
>> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
>> for user hive
>> 2016-05-11 13:31:17,099 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: metadata.HiveUtils
>> (HiveUtils.java:getMetaStoreAuthorizeProviderManagers(353)) - Adding
>> metastore authorization provider:
>> org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider
>> 2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool:
>> Thread-5867]: security.UserGroupInformation
>> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
>> for user hive
>> 2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool:
>> Thread-5867]: security.UserGroupInformation
>> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
>> for user hive
>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: ql.Driver (Driver.java:compile(466)) - Semantic
>> Analysis Completed
>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) -
>> </PERFLOG method=semanticAnalyze start=1462962677089
>> end=1462962677106 duration=17 from=org.apache.hadoop.hive.ql.Driver>
>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: ql.Driver (Driver.java:getSchema(246)) - Returning Hive
>> schema: Schema(fieldSchemas:null, properties:null)
>> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) -
>> <PERFLOG method=doAuthorization from=org.apache.hadoop.hive.ql.Driver>
>> 2016-05-11 13:31:17,107 WARN [HiveServer2-Handler-Pool:
>> Thread-5867]: security.UserGroupInformation
>> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
>> for user margusja
>> 2016-05-11 13:31:18,289 INFO
>> [org.apache.hadoop.util.JvmPauseMonitor$Monitor@59f45950]:
>> util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected pause
>> in JVM or host machine (eg GC): pause of approximately 1092ms
>> 2016-05-11 13:31:29,547 INFO [HiveServer2-Handler-Pool:
>> Thread-5867]: retry.RetryInvocationHandler
>> (RetryInvocationHandler.java:invoke(144)) - Exception while invoking
>> getListing of class ClientNamenodeProtocolTranslatorPB over
>> hadoopnn1.estpak.ee/88.196.164.42:8020. Trying to fail over immediately.
>> java.io.IOException: com.google.protobuf.ServiceException:
>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>> at
>> org.apache.hadoop.ipc.ProtobufHelper.getRemoteException(ProtobufHelper.java:47)
>> at
>> org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:580)
>> at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>> at java.lang.reflect.Method.invoke(Method.java:497)
>> at
>> org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:252)
>> at
>> org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:104)
>> at com.sun.proxy.$Proxy16.getListing(Unknown Source)
>> at
>> org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2094)
>> at
>> org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2077)
>> at
>> org.apache.hadoop.hdfs.DistributedFileSystem.listStatusInternal(DistributedFileSystem.java:832)
>> at
>> org.apache.hadoop.hdfs.DistributedFileSystem.access$700(DistributedFileSystem.java:106)
>> at
>> org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:863)
>> at
>> org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:859)
>> at
>> org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)
>> at
>> org.apache.hadoop.hdfs.DistributedFileSystem.listStatus(DistributedFileSystem.java:859)
>> at
>> org.apache.hadoop.hive.common.FileUtils.isOwnerOfFileHierarchy(FileUtils.java:481)
>> at
>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
>> at
>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
>> at
>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
>> at
>> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
>> at
>> org.apache.hadoop.hive.ql.Driver.doAuthorizationV2(Driver.java:817)
>> at
>> org.apache.hadoop.hive.ql.Driver.doAuthorization(Driver.java:608)
>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:499)
>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:314)
>> at
>> org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1164)
>> at
>> org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1158)
>> at
>> org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
>> at
>> org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181)
>> at
>> org.apache.hive.service.cli.operation.Operation.run(Operation.java:257)
>> at
>> org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:410)
>> at
>> org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:397)
>> at
>> org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:274)
>> at
>> org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486)
>> at
>> org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313)
>> at
>> org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298)
>> at
>> org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
>> at
>> org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
>> at
>> org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:692)
>> at
>> org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
>> at
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
>> at java.lang.Thread.run(Thread.java:745)
>> Caused by: com.google.protobuf.ServiceException:
>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>> at
>> org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:271)
>> at com.sun.proxy.$Proxy15.getListing(Unknown Source)
>> at
>> org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:573)
>> ... 39 more
>>
>> I have hdfs namenode high availability configured and automatic fail
>> over enabled. I can see that active namenode does not change during
>> the creating table process.
>>
>> Also I have hive high availability configured.
>>
>>
>>
>>
>>
>>
>>
>>
>> Margus (margusja) Roo
>> http://margus.roo.ee
>> skype: margusja
>> +372 51 48 780
>> On 11/05/16 12:26, Margus Roo wrote:
>>>
>>> Sadly in our environment:
>>>
>>>
>>> Generated files like you did.
>>>
>>> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
>>> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
>>> Transaction isolation: TRANSACTION_REPEATABLE_READ
>>> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado> create external table
>>> files_10k (i int) row format delimited fields terminated by '\t'
>>> location '/user/margusja/files_10k';
>>> Error: Shutdown in progress, cannot remove a shutdownHook
>>> (state=,code=0)
>>> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado>
>>>
>>> Using just hive:
>>>
>>> [margusja@hadoopnn1 ~]$ hive
>>> WARNING: Use "yarn jar" to launch YARN applications.
>>> log4j:WARN No such property [maxBackupIndex] in
>>> org.apache.log4j.DailyRollingFileAppender.
>>>
>>> Logging initialized using configuration in
>>> file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
>>> hive> create external table files_10k (i int) row format delimited
>>> fields terminated by '\t' location '/user/margusja/files_10k';
>>> OK
>>> Time taken: 1.255 seconds
>>> hive>
>>>
>>>
>>> Margus (margusja) Roo
>>> http://margus.roo.ee
>>> skype: margusja
>>> +372 51 48 780
>>> On 11/05/16 10:16, Markovitz, Dudu wrote:
>>>> create external table files_10k (i int) row format delimited fields
>>>> terminated by '\t' location '/tmp/files_10k';
>>>
>>
>
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
One more example:
[hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /user/margusja/files_10k/
1 9.8 K 47.7 K /user/margusja/files_10k
[hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /datasource/dealgate/
53 7.9 K 8.5 G /datasource/dealgate
2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> create external table
files_10k (i int) row format delimited fields terminated by '\t'
location '/user/margusja/files_10k';
No rows affected (0.197 seconds)
2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> drop table files_10k;
No rows affected (0.078 seconds)
2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def> create external table
files_10k (i int) row format delimited fields terminated by '\t'
location '/datasource/dealgate';
Error: org.apache.thrift.transport.TTransportException (state=08S01,code=0)
2: jdbc:hive2://hadoopnn1.estpak.ee:10000/def>
So in my point of view beeline in some reason looks data and old hive
client does not.
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 11/05/16 13:35, Margus Roo wrote:
>
> More information:
>
> 2016-05-11 13:31:17,086 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> parse.ParseDriver (ParseDriver.java:parse(185)) - Parsing command:
> create external table files_10k (i int) row format delimited fields
> terminated by '\t' location '/user/margusja/files_10k'
> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> parse.ParseDriver (ParseDriver.java:parse(209)) - Parse Completed
> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
> method=parse start=1462962677086 end=1462962677089 duration=3
> from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
> method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-11 13:31:17,090 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> parse.CalcitePlanner (SemanticAnalyzer.java:analyzeInternal(10114)) -
> Starting Semantic Analysis
> 2016-05-11 13:31:17,093 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> parse.CalcitePlanner (SemanticAnalyzer.java:analyzeCreateTable(10776))
> - Creating table default.files_10k position=22
> 2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> metastore.HiveMetaStore (HiveMetaStore.java:logInfo(747)) - 2:
> get_database: default
> 2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(372)) -
> ugi=hive/hadoopnn1.estpak.ee@TESTHADOOP.COM ip=unknown-ip-addr
> cmd=get_database: default
> 2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool: Thread-5867]:
> security.UserGroupInformation
> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
> for user hive
> 2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool: Thread-5867]:
> security.UserGroupInformation
> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
> for user hive
> 2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool: Thread-5867]:
> security.UserGroupInformation
> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
> for user hive
> 2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool: Thread-5867]:
> security.UserGroupInformation
> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
> for user hive
> 2016-05-11 13:31:17,099 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> metadata.HiveUtils
> (HiveUtils.java:getMetaStoreAuthorizeProviderManagers(353)) - Adding
> metastore authorization provider:
> org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider
> 2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool: Thread-5867]:
> security.UserGroupInformation
> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
> for user hive
> 2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool: Thread-5867]:
> security.UserGroupInformation
> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
> for user hive
> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> ql.Driver (Driver.java:compile(466)) - Semantic Analysis Completed
> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
> method=semanticAnalyze start=1462962677089 end=1462962677106
> duration=17 from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> ql.Driver (Driver.java:getSchema(246)) - Returning Hive schema:
> Schema(fieldSchemas:null, properties:null)
> 2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
> method=doAuthorization from=org.apache.hadoop.hive.ql.Driver>
> 2016-05-11 13:31:17,107 WARN [HiveServer2-Handler-Pool: Thread-5867]:
> security.UserGroupInformation
> (UserGroupInformation.java:getGroupNames(1521)) - No groups available
> for user margusja
> 2016-05-11 13:31:18,289 INFO
> [org.apache.hadoop.util.JvmPauseMonitor$Monitor@59f45950]:
> util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected pause
> in JVM or host machine (eg GC): pause of approximately 1092ms
> 2016-05-11 13:31:29,547 INFO [HiveServer2-Handler-Pool: Thread-5867]:
> retry.RetryInvocationHandler (RetryInvocationHandler.java:invoke(144))
> - Exception while invoking getListing of class
> ClientNamenodeProtocolTranslatorPB over
> hadoopnn1.estpak.ee/88.196.164.42:8020. Trying to fail over immediately.
> java.io.IOException: com.google.protobuf.ServiceException:
> java.lang.OutOfMemoryError: GC overhead limit exceeded
> at
> org.apache.hadoop.ipc.ProtobufHelper.getRemoteException(ProtobufHelper.java:47)
> at
> org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:580)
> at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:497)
> at
> org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:252)
> at
> org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:104)
> at com.sun.proxy.$Proxy16.getListing(Unknown Source)
> at org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2094)
> at org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2077)
> at
> org.apache.hadoop.hdfs.DistributedFileSystem.listStatusInternal(DistributedFileSystem.java:832)
> at
> org.apache.hadoop.hdfs.DistributedFileSystem.access$700(DistributedFileSystem.java:106)
> at
> org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:863)
> at
> org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:859)
> at
> org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)
> at
> org.apache.hadoop.hdfs.DistributedFileSystem.listStatus(DistributedFileSystem.java:859)
> at
> org.apache.hadoop.hive.common.FileUtils.isOwnerOfFileHierarchy(FileUtils.java:481)
> at
> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
> at
> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
> at
> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
> at
> org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
> at
> org.apache.hadoop.hive.ql.Driver.doAuthorizationV2(Driver.java:817)
> at
> org.apache.hadoop.hive.ql.Driver.doAuthorization(Driver.java:608)
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:499)
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:314)
> at
> org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1164)
> at
> org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1158)
> at
> org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
> at
> org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181)
> at
> org.apache.hive.service.cli.operation.Operation.run(Operation.java:257)
> at
> org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:410)
> at
> org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:397)
> at
> org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:274)
> at
> org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486)
> at
> org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313)
> at
> org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298)
> at
> org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
> at
> org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
> at
> org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:692)
> at
> org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: com.google.protobuf.ServiceException:
> java.lang.OutOfMemoryError: GC overhead limit exceeded
> at
> org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:271)
> at com.sun.proxy.$Proxy15.getListing(Unknown Source)
> at
> org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:573)
> ... 39 more
>
> I have hdfs namenode high availability configured and automatic fail
> over enabled. I can see that active namenode does not change during
> the creating table process.
>
> Also I have hive high availability configured.
>
>
>
>
>
>
>
>
> Margus (margusja) Roo
> http://margus.roo.ee
> skype: margusja
> +372 51 48 780
> On 11/05/16 12:26, Margus Roo wrote:
>>
>> Sadly in our environment:
>>
>>
>> Generated files like you did.
>>
>> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
>> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
>> Transaction isolation: TRANSACTION_REPEATABLE_READ
>> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado> create external table
>> files_10k (i int) row format delimited fields terminated by '\t'
>> location '/user/margusja/files_10k';
>> Error: Shutdown in progress, cannot remove a shutdownHook (state=,code=0)
>> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado>
>>
>> Using just hive:
>>
>> [margusja@hadoopnn1 ~]$ hive
>> WARNING: Use "yarn jar" to launch YARN applications.
>> log4j:WARN No such property [maxBackupIndex] in
>> org.apache.log4j.DailyRollingFileAppender.
>>
>> Logging initialized using configuration in
>> file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
>> hive> create external table files_10k (i int) row format delimited
>> fields terminated by '\t' location '/user/margusja/files_10k';
>> OK
>> Time taken: 1.255 seconds
>> hive>
>>
>>
>> Margus (margusja) Roo
>> http://margus.roo.ee
>> skype: margusja
>> +372 51 48 780
>> On 11/05/16 10:16, Markovitz, Dudu wrote:
>>> create external table files_10k (i int) row format delimited fields
>>> terminated by '\t' location '/tmp/files_10k';
>>
>
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
More information:
2016-05-11 13:31:17,086 INFO [HiveServer2-Handler-Pool: Thread-5867]:
parse.ParseDriver (ParseDriver.java:parse(185)) - Parsing command:
create external table files_10k (i int) row format delimited fields
terminated by '\t' location '/user/margusja/files_10k'
2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool: Thread-5867]:
parse.ParseDriver (ParseDriver.java:parse(209)) - Parse Completed
2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool: Thread-5867]:
log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
method=parse start=1462962677086 end=1462962677089 duration=3
from=org.apache.hadoop.hive.ql.Driver>
2016-05-11 13:31:17,089 INFO [HiveServer2-Handler-Pool: Thread-5867]:
log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver>
2016-05-11 13:31:17,090 INFO [HiveServer2-Handler-Pool: Thread-5867]:
parse.CalcitePlanner (SemanticAnalyzer.java:analyzeInternal(10114)) -
Starting Semantic Analysis
2016-05-11 13:31:17,093 INFO [HiveServer2-Handler-Pool: Thread-5867]:
parse.CalcitePlanner (SemanticAnalyzer.java:analyzeCreateTable(10776)) -
Creating table default.files_10k position=22
2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool: Thread-5867]:
metastore.HiveMetaStore (HiveMetaStore.java:logInfo(747)) - 2:
get_database: default
2016-05-11 13:31:17,094 INFO [HiveServer2-Handler-Pool: Thread-5867]:
HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(372)) -
ugi=hive/hadoopnn1.estpak.ee@TESTHADOOP.COM ip=unknown-ip-addr
cmd=get_database: default
2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool: Thread-5867]:
security.UserGroupInformation
(UserGroupInformation.java:getGroupNames(1521)) - No groups available
for user hive
2016-05-11 13:31:17,098 WARN [HiveServer2-Handler-Pool: Thread-5867]:
security.UserGroupInformation
(UserGroupInformation.java:getGroupNames(1521)) - No groups available
for user hive
2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool: Thread-5867]:
security.UserGroupInformation
(UserGroupInformation.java:getGroupNames(1521)) - No groups available
for user hive
2016-05-11 13:31:17,099 WARN [HiveServer2-Handler-Pool: Thread-5867]:
security.UserGroupInformation
(UserGroupInformation.java:getGroupNames(1521)) - No groups available
for user hive
2016-05-11 13:31:17,099 INFO [HiveServer2-Handler-Pool: Thread-5867]:
metadata.HiveUtils
(HiveUtils.java:getMetaStoreAuthorizeProviderManagers(353)) - Adding
metastore authorization provider:
org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider
2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool: Thread-5867]:
security.UserGroupInformation
(UserGroupInformation.java:getGroupNames(1521)) - No groups available
for user hive
2016-05-11 13:31:17,102 WARN [HiveServer2-Handler-Pool: Thread-5867]:
security.UserGroupInformation
(UserGroupInformation.java:getGroupNames(1521)) - No groups available
for user hive
2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
ql.Driver (Driver.java:compile(466)) - Semantic Analysis Completed
2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
log.PerfLogger (PerfLogger.java:PerfLogEnd(162)) - </PERFLOG
method=semanticAnalyze start=1462962677089 end=1462962677106 duration=17
from=org.apache.hadoop.hive.ql.Driver>
2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
ql.Driver (Driver.java:getSchema(246)) - Returning Hive schema:
Schema(fieldSchemas:null, properties:null)
2016-05-11 13:31:17,106 INFO [HiveServer2-Handler-Pool: Thread-5867]:
log.PerfLogger (PerfLogger.java:PerfLogBegin(135)) - <PERFLOG
method=doAuthorization from=org.apache.hadoop.hive.ql.Driver>
2016-05-11 13:31:17,107 WARN [HiveServer2-Handler-Pool: Thread-5867]:
security.UserGroupInformation
(UserGroupInformation.java:getGroupNames(1521)) - No groups available
for user margusja
2016-05-11 13:31:18,289 INFO
[org.apache.hadoop.util.JvmPauseMonitor$Monitor@59f45950]:
util.JvmPauseMonitor (JvmPauseMonitor.java:run(195)) - Detected pause in
JVM or host machine (eg GC): pause of approximately 1092ms
2016-05-11 13:31:29,547 INFO [HiveServer2-Handler-Pool: Thread-5867]:
retry.RetryInvocationHandler (RetryInvocationHandler.java:invoke(144)) -
Exception while invoking getListing of class
ClientNamenodeProtocolTranslatorPB over
hadoopnn1.estpak.ee/88.196.164.42:8020. Trying to fail over immediately.
java.io.IOException: com.google.protobuf.ServiceException:
java.lang.OutOfMemoryError: GC overhead limit exceeded
at
org.apache.hadoop.ipc.ProtobufHelper.getRemoteException(ProtobufHelper.java:47)
at
org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:580)
at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at
org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:252)
at
org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:104)
at com.sun.proxy.$Proxy16.getListing(Unknown Source)
at org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2094)
at org.apache.hadoop.hdfs.DFSClient.listPaths(DFSClient.java:2077)
at
org.apache.hadoop.hdfs.DistributedFileSystem.listStatusInternal(DistributedFileSystem.java:832)
at
org.apache.hadoop.hdfs.DistributedFileSystem.access$700(DistributedFileSystem.java:106)
at
org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:863)
at
org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:859)
at
org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)
at
org.apache.hadoop.hdfs.DistributedFileSystem.listStatus(DistributedFileSystem.java:859)
at
org.apache.hadoop.hive.common.FileUtils.isOwnerOfFileHierarchy(FileUtils.java:481)
at
org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
at
org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
at
org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.isURIAccessAllowed(RangerHiveAuthorizer.java:749)
at
org.apache.ranger.authorization.hive.authorizer.RangerHiveAuthorizer.checkPrivileges(RangerHiveAuthorizer.java:252)
at
org.apache.hadoop.hive.ql.Driver.doAuthorizationV2(Driver.java:817)
at
org.apache.hadoop.hive.ql.Driver.doAuthorization(Driver.java:608)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:499)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:314)
at
org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1164)
at
org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1158)
at
org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
at
org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181)
at
org.apache.hive.service.cli.operation.Operation.run(Operation.java:257)
at
org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:410)
at
org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:397)
at
org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:274)
at
org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486)
at
org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313)
at
org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298)
at
org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:692)
at
org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.google.protobuf.ServiceException:
java.lang.OutOfMemoryError: GC overhead limit exceeded
at
org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:271)
at com.sun.proxy.$Proxy15.getListing(Unknown Source)
at
org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getListing(ClientNamenodeProtocolTranslatorPB.java:573)
... 39 more
I have hdfs namenode high availability configured and automatic fail
over enabled. I can see that active namenode does not change during the
creating table process.
Also I have hive high availability configured.
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 11/05/16 12:26, Margus Roo wrote:
>
> Sadly in our environment:
>
>
> Generated files like you did.
>
> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado> create external table
> files_10k (i int) row format delimited fields terminated by '\t'
> location '/user/margusja/files_10k';
> Error: Shutdown in progress, cannot remove a shutdownHook (state=,code=0)
> 0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado>
>
> Using just hive:
>
> [margusja@hadoopnn1 ~]$ hive
> WARNING: Use "yarn jar" to launch YARN applications.
> log4j:WARN No such property [maxBackupIndex] in
> org.apache.log4j.DailyRollingFileAppender.
>
> Logging initialized using configuration in
> file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
> hive> create external table files_10k (i int) row format delimited
> fields terminated by '\t' location '/user/margusja/files_10k';
> OK
> Time taken: 1.255 seconds
> hive>
>
>
> Margus (margusja) Roo
> http://margus.roo.ee
> skype: margusja
> +372 51 48 780
> On 11/05/16 10:16, Markovitz, Dudu wrote:
>> create external table files_10k (i int) row format delimited fields
>> terminated by '\t' location '/tmp/files_10k';
>
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
Sadly in our environment:
Generated files like you did.
Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado> create external table
files_10k (i int) row format delimited fields terminated by '\t'
location '/user/margusja/files_10k';
Error: Shutdown in progress, cannot remove a shutdownHook (state=,code=0)
0: jdbc:hive2://hadoopnn1.estpak.ee:2181,hado>
Using just hive:
[margusja@hadoopnn1 ~]$ hive
WARNING: Use "yarn jar" to launch YARN applications.
log4j:WARN No such property [maxBackupIndex] in
org.apache.log4j.DailyRollingFileAppender.
Logging initialized using configuration in
file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
hive> create external table files_10k (i int) row format delimited
fields terminated by '\t' location '/user/margusja/files_10k';
OK
Time taken: 1.255 seconds
hive>
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 11/05/16 10:16, Markovitz, Dudu wrote:
> create external table files_10k (i int) row format delimited fields
> terminated by '\t' location '/tmp/files_10k';
RE: Create external table
Posted by "Markovitz, Dudu" <dm...@paypal.com>.
Could not reproduced that issue on Cloudera quickstart VM.
I’ve created an HDFS directory with 10,000 files.
I’ve create external table from within beeline.
The creation was immediate.
Dudu
-------------------------------------------------------------------
bash
-------------------------------------------------------------------
mkdir files_10k
awk 'BEGIN{for (i=1;i<=10000;++i){print i>"./files_10k/f"i".txt"}}'
hdfs dfs -put files_10k /tmp
-------------------------------------------------------------------
beeline
-------------------------------------------------------------------
> create external table files_10k (i int) row format delimited fields terminated by '\t' location '/tmp/files_10k';
No rows affected (0.282 seconds)
> select * from files_10k;
10,000 rows selected (27.986 seconds)
From: Margus Roo [mailto:margus@roo.ee]
Sent: Tuesday, May 10, 2016 11:26 PM
To: user@hive.apache.org
Subject: Re: Create external table
Hi again
I opened hive (an old client)
And exactly the same create external table .... location [paht in hdfs to place where are loads of files] works and the same DDL does not work via beeline.
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 10/05/16 23:03, Margus Roo wrote:
Hi
Can someone explain or provide documentation how Hive creates external tables?
I have problem with creating external table in case I am pointing location in hdfs in to directory where are loads of files. Beeline just hangs or there will be other errors.
In case I point location in to the empty directory then hive creates table.
So does hive looks into files during creating external table?
I can not find any documentation explaining it.
--
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
What do you mean?
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 11/05/16 08:21, Mich Talebzadeh wrote:
> yes but table then exists correct I mean second time
>
> did you try
>
>
> *use default;*
> *
> drop table if exists trips;*
> **
>
>
> it is still within Hive metadata registered as an existing table.
> *
> *
>
>
> *
>
>
>
> *
>
> Dr Mich Talebzadeh
>
> LinkedIn
> /https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw/
>
> http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>
>
>
> On 11 May 2016 at 06:16, Margus Roo <margus@roo.ee
> <ma...@roo.ee>> wrote:
>
> Hi
>
> Thanks for your answer.
>
> ---
>
> At first I create an empty hdfs directory (if directory is empty I
> did not have problems before too).
>
> [margusja@hadoopnn1 ~]$ hdfs dfs -mkdir /user/margusja/trips
>
> [margusja@hadoopnn1 ~]$ beeline -f create_externat_table_trips.hql
> -u
> "jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM"
> <ma...@TESTHADOOP.COM>
> --verbose
> WARNING: Use "yarn jar" to launch YARN applications.
> issuing: !connect jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hadoopnn2.example.com:2181
> <http://hadoopnn2.example.com:2181>,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
> <ma...@TESTHADOOP.COM>
> '' [passwd stripped]
> Connecting to jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hadoopnn2.example.com:2181
> <http://hadoopnn2.example.com:2181>,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
> <ma...@TESTHADOOP.COM>
> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> use default;
> Getting log thread is interrupted, since query is done!
> No rows affected (1.225 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> drop table if exists trips;
> Getting log thread is interrupted, since query is done!
> No rows affected (0.159 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> CREATE EXTERNAL TABLE
> `TRIPS`(
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> `bike_nr` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> `duration` int,
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> `start_date` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> `start_station` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> `end_station` string)
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> PARTITIONED BY (
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> `year` int,
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> `month` string)
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> ROW FORMAT DELIMITED
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> FIELDS TERMINATED BY ','
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> LINES TERMINATED BY '\n'
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> STORED AS INPUTFORMAT
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado>
> 'org.apache.hadoop.mapred.TextInputFormat'
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> OUTPUTFORMAT
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado>
> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> LOCATION
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> '/user/margusja/trips';
> Getting log thread is interrupted, since query is done!
> No rows affected (0.067 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> desc trips;
> Getting log thread is interrupted, since query is done!
> +--------------------------+-----------------------+-----------------------+--+
> | col_name | data_type | comment |
> +--------------------------+-----------------------+-----------------------+--+
> | bike_nr | string | |
> | duration | int | |
> | start_date | string | |
> | start_station | string | |
> | end_station | string | |
> | year | int | |
> | month | string | |
> | | NULL |
> NULL |
> | # Partition Information | NULL |
> NULL |
> | # col_name | data_type |
> comment |
> | | NULL |
> NULL |
> | year | int | |
> | month | string | |
> +--------------------------+-----------------------+-----------------------+--+
> 13 rows selected (0.46 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hado> !exit
> Closing: 0: jdbc:hive2://hadoopnn1.example.com:2181
> <http://hadoopnn1.example.com:2181>,hadoopnn2.example.com:2181
> <http://hadoopnn2.example.com:2181>,hadoopdn1.host.ee:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
> <ma...@TESTHADOOP.COM>
>
>
> But In case I try:
>
> CREATE EXTERNAL TABLE default.st1_test_margusja (
> original STRING,
> rsyslog_timestamp STRING,
> rsyslog_url STRING,
> rsyslog_appname STRING,
> rsyslog_pos5 STRING,
> ts STRING,
> url STRING,
> username STRING,
> ip_address STRING,
> log_level STRING,
> content STRING
> )
> COMMENT 'Dealgate logs raw data'
> PARTITIONED BY (year STRING, month STRING, day STRING)
> ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
> WITH SERDEPROPERTIES ('input.regex'='([^\\s]+ ([^\\s]+)
> ([^\\s]+) ([^\\s]+) ([^\\s]+) [^\\s]+
> [^\\s]+[\\s]{1,2}([^\\|]+)\\|([^\\|]+)\\|([^\\+]*)\\+([^\\|]*)\\|([^\\|]+)\\|(.+))'
> )
> STORED AS TEXTFILE
> LOCATION '/datasource/test/';
>
> Now I am starting to see loads of GC warnings in hiveserver2 log
> and sometomes beeline ends: Error:
> org.apache.thrift.transport.TTransportException (state=08S01,code=0)
>
> [hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /datasource/test/
> 53 7.2 K 8.4 G /datasource/test
>
> And now I use hive:
>
> [margusja@hadoopnn1 ~]$ hive
> WARNING: Use "yarn jar" to launch YARN applications.
> log4j:WARN No such property [maxBackupIndex] in
> org.apache.log4j.DailyRollingFileAppender.
>
> Logging initialized using configuration in
> file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
> hive> CREATE EXTERNAL TABLE default.st1_test_margusja (
> > original STRING,
> > rsyslog_timestamp STRING,
> > rsyslog_url STRING,
> > rsyslog_appname STRING,
> > rsyslog_pos5 STRING,
> > ts STRING,
> > url STRING,
> > username STRING,
> > ip_address STRING,
> > log_level STRING,
> > content STRING
> > )
> > COMMENT 'Dealgate logs raw data'
> > PARTITIONED BY (year STRING, month STRING, day STRING)
> > ROW FORMAT SERDE
> 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
> > WITH SERDEPROPERTIES ('input.regex'='([^\\s]+ ([^\\s]+)
> ([^\\s]+) ([^\\s]+) ([^\\s]+) [^\\s]+
> [^\\s]+[\\s]{1,2}([^\\|]+)\\|([^\\|]+)\\|([^\\+]*)\\+([^\\|]*)\\|([^\\|]+)\\|(.+))'
> )
> > STORED AS TEXTFILE
> > LOCATION '/datasource/test/';
> OK
> Time taken: 1.254 seconds
> hive>
>
>
> Any hint is welcome
>
>
>
> Margus (margusja) Roo
> http://margus.roo.ee
> skype: margusja
> +372 51 48 780 <tel:%2B372%2051%2048%20780>
>
> On 11/05/16 02:16, Mich Talebzadeh wrote:
>> *use default;
>> drop table if exists trips;
>> CREATE EXTERNAL TABLE `TRIPS`(
>> `bike_nr` string,
>> `duration` int,
>> `start_date` string,
>> `start_station` string,
>> `end_station` string)
>> PARTITIONED BY (
>> `year` int,
>> `month` string)
>> ROW FORMAT DELIMITED
>> FIELDS TERMINATED BY ','
>> LINES TERMINATED BY '\n'
>> STORED AS INPUTFORMAT
>> 'org.apache.hadoop.mapred.TextInputFormat'
>> OUTPUTFORMAT
>> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
>> LOCATION
>> '/test/text/trips';*
>
>
Re: Create external table
Posted by Mich Talebzadeh <mi...@gmail.com>.
yes but table then exists correct I mean second time
did you try
*use default;*
*drop table if exists trips;*
it is still within Hive metadata registered as an existing table.
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 11 May 2016 at 06:16, Margus Roo <ma...@roo.ee> wrote:
> Hi
>
> Thanks for your answer.
>
> ---
>
> At first I create an empty hdfs directory (if directory is empty I did not
> have problems before too).
>
> [margusja@hadoopnn1 ~]$ hdfs dfs -mkdir /user/margusja/trips
>
> [margusja@hadoopnn1 ~]$ beeline -f create_externat_table_trips.hql -u
> "jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM"
> <jd...@TESTHADOOP.COM>
> --verbose
> WARNING: Use "yarn jar" to launch YARN applications.
> issuing: !connect jdbc:hive2://hadoopnn1.example.com:2181,
> hadoopnn2.example.com:2181,
> hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
> '' [passwd stripped]
> Connecting to jdbc:hive2://hadoopnn1.example.com:2181,
> hadoopnn2.example.com:2181,
> hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> use default;
> Getting log thread is interrupted, since query is done!
> No rows affected (1.225 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> drop table if exists
> trips;
> Getting log thread is interrupted, since query is done!
> No rows affected (0.159 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> CREATE EXTERNAL TABLE
> `TRIPS`(
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `bike_nr` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `duration` int,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `start_date` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `start_station` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `end_station` string)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> PARTITIONED BY (
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `year` int,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `month` string)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> ROW FORMAT DELIMITED
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> FIELDS TERMINATED BY
> ','
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> LINES TERMINATED BY
> '\n'
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> STORED AS INPUTFORMAT
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>
> 'org.apache.hadoop.mapred.TextInputFormat'
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> OUTPUTFORMAT
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>
> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> LOCATION
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> '/user/margusja/trips';
> Getting log thread is interrupted, since query is done!
> No rows affected (0.067 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> desc trips;
> Getting log thread is interrupted, since query is done!
>
> +--------------------------+-----------------------+-----------------------+--+
> | col_name | data_type | comment
> |
>
> +--------------------------+-----------------------+-----------------------+--+
> | bike_nr | string |
> |
> | duration | int |
> |
> | start_date | string |
> |
> | start_station | string |
> |
> | end_station | string |
> |
> | year | int |
> |
> | month | string |
> |
> | | NULL | NULL
> |
> | # Partition Information | NULL | NULL
> |
> | # col_name | data_type | comment
> |
> | | NULL | NULL
> |
> | year | int |
> |
> | month | string |
> |
>
> +--------------------------+-----------------------+-----------------------+--+
> 13 rows selected (0.46 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> !exit
> Closing: 0: jdbc:hive2://hadoopnn1.example.com:2181,
> hadoopnn2.example.com:2181,
> hadoopdn1.host.ee:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
>
>
> But In case I try:
>
> CREATE EXTERNAL TABLE default.st1_test_margusja (
> original STRING,
> rsyslog_timestamp STRING,
> rsyslog_url STRING,
> rsyslog_appname STRING,
> rsyslog_pos5 STRING,
> ts STRING,
> url STRING,
> username STRING,
> ip_address STRING,
> log_level STRING,
> content STRING
> )
> COMMENT 'Dealgate logs raw data'
> PARTITIONED BY (year STRING, month STRING, day STRING)
> ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
> WITH SERDEPROPERTIES ('input.regex'='([^\\s]+ ([^\\s]+) ([^\\s]+)
> ([^\\s]+) ([^\\s]+) [^\\s]+
> [^\\s]+[\\s]{1,2}([^\\|]+)\\|([^\\|]+)\\|([^\\+]*)\\+([^\\|]*)\\|([^\\|]+)\\|(.+))'
> )
> STORED AS TEXTFILE
> LOCATION '/datasource/test/';
>
> Now I am starting to see loads of GC warnings in hiveserver2 log and
> sometomes beeline ends: Error:
> org.apache.thrift.transport.TTransportException (state=08S01,code=0)
>
> [hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /datasource/test/
> 53 7.2 K 8.4 G /datasource/test
>
> And now I use hive:
>
> [margusja@hadoopnn1 ~]$ hive
> WARNING: Use "yarn jar" to launch YARN applications.
> log4j:WARN No such property [maxBackupIndex] in
> org.apache.log4j.DailyRollingFileAppender.
>
> Logging initialized using configuration in
> file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
> hive> CREATE EXTERNAL TABLE default.st1_test_margusja (
> > original STRING,
> > rsyslog_timestamp STRING,
> > rsyslog_url STRING,
> > rsyslog_appname STRING,
> > rsyslog_pos5 STRING,
> > ts STRING,
> > url STRING,
> > username STRING,
> > ip_address STRING,
> > log_level STRING,
> > content STRING
> > )
> > COMMENT 'Dealgate logs raw data'
> > PARTITIONED BY (year STRING, month STRING, day STRING)
> > ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
> > WITH SERDEPROPERTIES ('input.regex'='([^\\s]+ ([^\\s]+) ([^\\s]+)
> ([^\\s]+) ([^\\s]+) [^\\s]+
> [^\\s]+[\\s]{1,2}([^\\|]+)\\|([^\\|]+)\\|([^\\+]*)\\+([^\\|]*)\\|([^\\|]+)\\|(.+))'
> )
> > STORED AS TEXTFILE
> > LOCATION '/datasource/test/';
> OK
> Time taken: 1.254 seconds
> hive>
>
>
> Any hint is welcome
>
>
>
> Margus (margusja) Roohttp://margus.roo.ee
> skype: margusja+372 51 48 780
>
> On 11/05/16 02:16, Mich Talebzadeh wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *use default; drop table if exists trips; CREATE EXTERNAL TABLE `TRIPS`(
> `bike_nr` string, `duration` int, `start_date` string,
> `start_station` string, `end_station` string) PARTITIONED BY ( `year`
> int, `month` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
> LINES TERMINATED BY '\n' STORED AS INPUTFORMAT
> 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION
> '/test/text/trips';*
>
>
>
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
Hi
Thanks for your answer.
---
At first I create an empty hdfs directory (if directory is empty I did
not have problems before too).
[margusja@hadoopnn1 ~]$ hdfs dfs -mkdir /user/margusja/trips
[margusja@hadoopnn1 ~]$ beeline -f create_externat_table_trips.hql -u
"jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM"
--verbose
WARNING: Use "yarn jar" to launch YARN applications.
issuing: !connect
jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
'' [passwd stripped]
Connecting to
jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> use default;
Getting log thread is interrupted, since query is done!
No rows affected (1.225 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> drop table if exists trips;
Getting log thread is interrupted, since query is done!
No rows affected (0.159 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> CREATE EXTERNAL TABLE
`TRIPS`(
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `bike_nr` string,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `duration` int,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `start_date` string,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `start_station` string,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `end_station` string)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> PARTITIONED BY (
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `year` int,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `month` string)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> ROW FORMAT DELIMITED
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> FIELDS TERMINATED BY ','
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> LINES TERMINATED BY '\n'
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> STORED AS INPUTFORMAT
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>
'org.apache.hadoop.mapred.TextInputFormat'
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> OUTPUTFORMAT
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> LOCATION
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> '/user/margusja/trips';
Getting log thread is interrupted, since query is done!
No rows affected (0.067 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> desc trips;
Getting log thread is interrupted, since query is done!
+--------------------------+-----------------------+-----------------------+--+
| col_name | data_type | comment |
+--------------------------+-----------------------+-----------------------+--+
| bike_nr | string | |
| duration | int | |
| start_date | string | |
| start_station | string | |
| end_station | string | |
| year | int | |
| month | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| | NULL | NULL |
| year | int | |
| month | string | |
+--------------------------+-----------------------+-----------------------+--+
13 rows selected (0.46 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> !exit
Closing: 0:
jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.host.ee:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_HOST@TESTHADOOP.COM
But In case I try:
CREATE EXTERNAL TABLE default.st1_test_margusja (
original STRING,
rsyslog_timestamp STRING,
rsyslog_url STRING,
rsyslog_appname STRING,
rsyslog_pos5 STRING,
ts STRING,
url STRING,
username STRING,
ip_address STRING,
log_level STRING,
content STRING
)
COMMENT 'Dealgate logs raw data'
PARTITIONED BY (year STRING, month STRING, day STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ('input.regex'='([^\\s]+ ([^\\s]+) ([^\\s]+)
([^\\s]+) ([^\\s]+) [^\\s]+
[^\\s]+[\\s]{1,2}([^\\|]+)\\|([^\\|]+)\\|([^\\+]*)\\+([^\\|]*)\\|([^\\|]+)\\|(.+))'
)
STORED AS TEXTFILE
LOCATION '/datasource/test/';
Now I am starting to see loads of GC warnings in hiveserver2 log and
sometomes beeline ends: Error:
org.apache.thrift.transport.TTransportException (state=08S01,code=0)
[hdfs@hadoopnn1 ~]$ hdfs dfs -count -h /datasource/test/
53 7.2 K 8.4 G /datasource/test
And now I use hive:
[margusja@hadoopnn1 ~]$ hive
WARNING: Use "yarn jar" to launch YARN applications.
log4j:WARN No such property [maxBackupIndex] in
org.apache.log4j.DailyRollingFileAppender.
Logging initialized using configuration in
file:/etc/hive/2.3.4.0-3485/0/hive-log4j.properties
hive> CREATE EXTERNAL TABLE default.st1_test_margusja (
> original STRING,
> rsyslog_timestamp STRING,
> rsyslog_url STRING,
> rsyslog_appname STRING,
> rsyslog_pos5 STRING,
> ts STRING,
> url STRING,
> username STRING,
> ip_address STRING,
> log_level STRING,
> content STRING
> )
> COMMENT 'Dealgate logs raw data'
> PARTITIONED BY (year STRING, month STRING, day STRING)
> ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
> WITH SERDEPROPERTIES ('input.regex'='([^\\s]+ ([^\\s]+)
([^\\s]+) ([^\\s]+) ([^\\s]+) [^\\s]+
[^\\s]+[\\s]{1,2}([^\\|]+)\\|([^\\|]+)\\|([^\\+]*)\\+([^\\|]*)\\|([^\\|]+)\\|(.+))'
)
> STORED AS TEXTFILE
> LOCATION '/datasource/test/';
OK
Time taken: 1.254 seconds
hive>
Any hint is welcome
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 11/05/16 02:16, Mich Talebzadeh wrote:
> *use default;
> drop table if exists trips;
> CREATE EXTERNAL TABLE `TRIPS`(
> `bike_nr` string,
> `duration` int,
> `start_date` string,
> `start_station` string,
> `end_station` string)
> PARTITIONED BY (
> `year` int,
> `month` string)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ','
> LINES TERMINATED BY '\n'
> STORED AS INPUTFORMAT
> 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> LOCATION
> '/test/text/trips';*
Re: Create external table
Posted by Mich Talebzadeh <mi...@gmail.com>.
Try this simple external table creation in beeline (check first that that
it connects OK)
*use default;drop table if exists trips;CREATE EXTERNAL TABLE `TRIPS`(
`bike_nr` string, `duration` int, `start_date` string, `start_station`
string, `end_station` string)PARTITIONED BY ( `year` int, `month`
string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY
'\n'STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION
'/test/text/trips';desc trips;!exit*
run the code
bl -f create_externat_table_trips.hql
No rows affected (0.018 seconds)
CREATE EXTERNAL TABLE `TRIPS`(
`bike_nr` string,
`duration` int,
`start_date` string,
`start_station` string,
`end_station` string)
PARTITIONED BY (
`year` int,
`month` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'/test/text/trips'
INFO : Semantic Analysis Completed
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling
command(queryId=hduser_20160511001257_b2b612dd-d007-406d-a49c-83e8352a6ac1);
Time taken: 0.009 seconds
INFO : Executing
command(queryId=hduser_20160511001257_b2b612dd-d007-406d-a49c-83e8352a6ac1):
CREATE EXTERNAL TABLE `TRIPS`(
`bike_nr` string,
`duration` int,
`start_date` string,
`start_station` string,
`end_station` string)
PARTITIONED BY (
`year` int,
`month` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'/test/text/trips'
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hduser_20160511001257_b2b612dd-d007-406d-a49c-83e8352a6ac1);
Time taken: 0.028 seconds
INFO : OK
+--------------------------+-----------------------+-----------------------+--+
| col_name | data_type | comment |
+--------------------------+-----------------------+-----------------------+--+
| bike_nr | string | |
| duration | int | |
| start_date | string | |
| start_station | string | |
| end_station | string | |
| year | int | |
| month | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| | NULL | NULL |
| year | int | |
| month | string | |
+--------------------------+-----------------------+-----------------------+--+
13 rows selected (0.13 seconds)
0: jdbc:hive2://rhes564:10010/default> Closing: 0:
jdbc:hive2://rhes564:10010/default
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 10 May 2016 at 21:25, Margus Roo <ma...@roo.ee> wrote:
> Hi again
>
> I opened hive (an old client)
>
> And exactly the same create external table .... location [paht in hdfs to
> place where are loads of files] works and the same DDL does not work via
> beeline.
>
> Margus (margusja) Roohttp://margus.roo.ee
> skype: margusja+372 51 48 780
>
> On 10/05/16 23:03, Margus Roo wrote:
>
> Hi
>
> Can someone explain or provide documentation how Hive creates external
> tables?
>
> I have problem with creating external table in case I am pointing location
> in hdfs in to directory where are loads of files. Beeline just hangs or
> there will be other errors.
>
> In case I point location in to the empty directory then hive creates table.
>
>
> So does hive looks into files during creating external table?
>
> I can not find any documentation explaining it.
>
> --
> Margus (margusja) Roohttp://margus.roo.ee
> skype: margusja+372 51 48 780
>
>
>
Re: Create external table
Posted by Margus Roo <ma...@roo.ee>.
Hi again
I opened hive (an old client)
And exactly the same create external table .... location [paht in hdfs
to place where are loads of files] works and the same DDL does not work
via beeline.
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780
On 10/05/16 23:03, Margus Roo wrote:
>
> Hi
>
> Can someone explain or provide documentation how Hive creates external
> tables?
>
> I have problem with creating external table in case I am pointing
> location in hdfs in to directory where are loads of files. Beeline
> just hangs or there will be other errors.
>
> In case I point location in to the empty directory then hive creates
> table.
>
>
> So does hive looks into files during creating external table?
>
> I can not find any documentation explaining it.
>
> --
> Margus (margusja) Roo
> http://margus.roo.ee
> skype: margusja
> +372 51 48 780