Recreate SYSTEM tables Hbase

[#1]

Hi!
I'm trying to recreate my SYSTEM tables since I deleted them from HDFS. So I cleaned everyhing from HDFS /apps/hbase + I did

$ hbase zkCli clean -cleanAll

+ I delete everyhing from Zookeeper in /hbase-unsecure and afterwards start Hbase master.

When I tried to query one new created table in Phoenix I got this

org.apache.phoenix.exception.PhoenixIOException: Table 'SYSTEM.CATALOG' was not found, got: AFM_49_CLICKS_ANTIFRAUDD.

In my zookeeper I have this:

$ ls /hbase-unsecure/table
[hbase:meta, hbase:namespace, AFM_49_CLICKS_ANTIFRAUDD]

So I don't know how to recreate all of the SYSTEM tables in my HBASE. I tried many things, with hbck options -repair, -fix/fixAssignments and many others, but nothing really happend.

Can someone help me how to recreate this tables? Is that even possible? How to continue using Phoenix?
Please don't suggest me to delete znode from zookeeper and etc because I tried everyhing and nothing is creating SYSTEM tables on HDFS.

[#2]

Which version of HDP are you using ?

Can you post the full stack trace ?

[#3]

Hi @Ted Yu

ng HDP2.6.0.
So, when type "list" in shell or zeppelin I'm getting ZERO tables as a result.
When I try to create table I'm getting this:

org.apache.phoenix.exception.PhoenixIOException: Table 'SYSTEM.CATALOG' was not found, got: AFM_49_CLICKS_ANTIFRAUDD.
	at org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:111)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1303)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1268)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.createTable(ConnectionQueryServicesImpl.java:1464)
	at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:2190)
	at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:872)
	at org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:194)
	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:343)
	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:331)
	at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
	at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:329)
	at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1440)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
	at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:580)
	at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
	at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:94)
	at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:489)
	at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
	at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
	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: org.apache.hadoop.hbase.TableNotFoundException: Table 'SYSTEM.CATALOG' was not found, got: AFM_49_CLICKS_ANTIFRAUDD1.
	at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.locateRegionInMeta(ConnectionManager.java:1284)
	at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.locateRegion(ConnectionManager.java:1165)
	at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.locateRegion(ConnectionManager.java:1149)
	at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.locateRegion(ConnectionManager.java:1106)
	at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.getRegionLocation(ConnectionManager.java:941)
	at org.apache.hadoop.hbase.client.HRegionLocator.getRegionLocation(HRegionLocator.java:83)
	at org.apache.hadoop.hbase.client.HTable.getRegionLocation(HTable.java:504)
	at org.apache.hadoop.hbase.client.HTable.getKeysAndRegionsInRange(HTable.java:720)
	at org.apache.hadoop.hbase.client.HTable.getKeysAndRegionsInRange(HTable.java:690)
	at org.apache.hadoop.hbase.client.HTable.getStartKeysInRange(HTable.java:1757)
	at org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1712)
	at org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1692)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1286)
	... 25 more

The same table gets created, because I when I "list" its there but its not in SYSTEM.CATALOG since there is no such table in HBase.

When I try to make SELECT query on the same table, this is what I'm getting:

org.apache.phoenix.exception.PhoenixIOException: Table 'SYSTEM.CATALOG' was not found, got: AFM_49_CLICKS_ANTIFRAUDD.
	at org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:111)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1303)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1268)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.getTable(ConnectionQueryServicesImpl.java:1493)
	at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:514)
	at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:437)
	at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:429)
	at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:425)
	at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:535)
	at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.<init>(FromCompiler.java:365)
	at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:213)
	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:397)
	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:378)
	at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:271)
	at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266)
	at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)

[#4]

I cannot find anything, but any little thing how to recreate system tables, can you please help me I' literally driving nuts 😞

[#5 · Accepted solution]

@Ivan Majnaric,

Did you try running sqlline.py. It will create the tables for you.

(/usr/hdp/current/phoenix-client/bin/sqlline.py)

Thanks,

Aditya

[#6]

I was on the edge with time, so I created Phoenix server on the other node and he created all SYSTEM tables he needed.
On the other side, I haven't knew for that .py file. Does he really creates SYSTEM files? I don't want to run the same file because now its all working, or if I run it nothing will happen? If so, I can mark your answer as accepted.

Also, thank you for your time 🙂

[#7]

@Ivan Majnaric,

There is no harm in running sqlline.py again. Actually it is a client to query phoenix. It will create the SYSTEM tables if not already created. You can check this answer by Josh in the link.

https://community.hortonworks.com/questions/64005/phoenix-security-and-initial-system-table-creation...

If this works for you please mark the answer as accepted so that it will be useful for the community.

Thanks,

Aditya