Apache Impala database增删
创建数据库
CREATE DATABASE语句用于在Impala中创建新数据库。
CREATE DATABASE IF NOT EXISTS database_name;
这里，IF NOT EXISTS是一个可选的子句。如果我们使用此子句，则只有在没有具有相同名称的现有数据库时，才会创建具有给定名称的数据库。
[hadoop03.Hadoop.com:21000] > create database if not exists hahaha;
Query: create database if not exists hahaha
ERROR: ImpalaRuntimeException: Error making 'createDatabase' RPC to Hive Metastore:
CAUSED BY: MetaException: Got exception: org.apache.hadoop.security.AccessControlException Permission denied: user=impala, access=WRITE, inode="/user/hive/warehouse":root:supergroup:drwxr-xr-x
at org.apache.hadoop.hdfs.server.namenode.DefaultAuthorizationProvider.checkFsPermission(DefaultAuthorizationProvider.java:279)
at org.apache.hadoop.hdfs.server.namenode.DefaultAuthorizationProvider.check(DefaultAuthorizationProvider.java:260)
at org.apache.hadoop.hdfs.server.namenode.DefaultAuthorizationProvider.check(DefaultAuthorizationProvider.java:240)
at org.apache.hadoop.hdfs.server.namenode.DefaultAuthorizationProvider.checkPermission(DefaultAuthorizationProvider.java:162)
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:152)
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPermission(FSDirectory.java:3877)
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPermission(FSDirectory.java:3860)
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkAncestorAccess(FSDirectory.java:3842)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkAncestorAccess(FSNamesystem.java:6744)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.mkdirsInternal(FSNamesystem.java:4502)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.mkdirsInt(FSNamesystem.java:4472)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.mkdirs(FSNamesystem.java:4445)
at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.mkdirs(NameNodeRpcServer.java:880)
at org.apache.hadoop.hdfs.server.namenode.AuthorizationProviderProxyClientProtocol.mkdirs(AuthorizationProviderProxyClientProtocol.java:326)
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.mkdirs(ClientNamenodeProtocolServerSideTranslatorPB.java:640)
at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java)
at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:617)
at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:1073)
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2281)
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2277)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1917)
at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2275)
[hadoop03.Hadoop.com:21000] >
impala默认使用impala用户执行操作，会报权限不足问题，解决办法：
一：给HDFS指定文件夹授予权限
hadoop fs -chmod -R 777 hdfs://hadoop01:9000/user/hive
我运行却拒绝连接了= =
[root@hadoop01 sbin]# hadoop fs -chmod -R 777 hdfs://hadoop01:9000/user/hive
chmod: Call From hadoop01.Hadoop.com/192.168.100.201 to hadoop01:9000 failed on connection exception: java.net.ConnectException: 拒绝连接; For more details see: http://wiki.apache.org/hadoop/ConnectionRefused
二：haoop 配置文件中hdfs-site.xml 中设置权限为false
<property>
<name>dfs.permissions</name>
<value>false</value>
</property>
上述两种方式都可以。
只好使用第二种方式，就成功了。
[hadoop03.Hadoop.com:21000] > create database hahaha;
Query: create database hahaha
Fetched 0 row(s) in 2.61s
[hadoop03.Hadoop.com:21000] > show databases;
Query: show databases
+------------------+----------------------------------------------+
| name | comment |
+------------------+----------------------------------------------+
| _impala_builtins | System database for Impala builtin functions |
| accumulate | |
| default | Default Hive database |
| hahaha | |
| myhive | |
| myhive2 | |
| test | |
| testdb | |
+------------------+----------------------------------------------+
Fetched 8 row(s) in 0.01s
[hadoop03.Hadoop.com:21000] >
默认就会在hive的数仓路径下创建新的数据库名文件夹
/user/hive/warehouse/ittest.db
[root@hadoop01 sbin]# hadoop fs -ls /user/hive/warehouse
Found 6 items
drwxr-xr-x - root supergroup 0 2019-11-22 15:29 /user/hive/warehouse/accumulate.db
drwxr-xr-x - impala supergroup 0 2019-12-10 20:18 /user/hive/warehouse/hahaha.db
drwxr-xr-x - root supergroup 0 2019-11-21 09:25 /user/hive/warehouse/myhive.db
drwxr-xr-x - root supergroup 0 2019-11-22 14:28 /user/hive/warehouse/myhive2.db
drwxr-xr-x - root supergroup 0 2019-11-29 16:56 /user/hive/warehouse/test.db
drwxr-xr-x - root supergroup 0 2019-11-27 17:41 /user/hive/warehouse/testdb.db
[root@hadoop01 sbin]#
也可以在创建数据库的时候指定hdfs路径。需要注意该路径的权限。
hadoop fs -mkdir -p /input/impala
hadoop fs -chmod -R 777 /input/impala
create external table t3(id int ,name string ,age int ) row format delimited fields terminated by '\t' location '/input/impala/external';
[hadoop03.Hadoop.com:21000] > create external table t3(id int ,name string ,age int ) row format delimited fields terminated by '\t' location '/input/impala/external';
Query: create external table t3(id int ,name string ,age int ) row format delimited fields terminated by '\t' location '/input/impala/external'
Fetched 0 row(s) in 1.34s
删除数据库
Impala的DROP DATABASE语句用于从Impala中删除数据库。 在删除数据库之前，建议从中删除所有表。
drop database hahaha;
[hadoop03.Hadoop.com:21000] > drop database hahaha;
Query: drop database hahaha
ERROR: ImpalaRuntimeException: Error making 'dropDatabase' RPC to Hive Metastore:
CAUSED BY: InvalidOperationException: Database hahaha is not empty. One or more tables exist.
如果使用级联删除，Impala会在删除指定数据库中的表之前删除它。
drop database hahaha cascade;
[hadoop03.Hadoop.com:21000] > drop database hahaha cascade;
Query: drop database hahaha cascade
