Reply
New Contributor
Posts: 1
Registered: ‎07-20-2017

Phoenix Table Creation. Error on recreating tables with a Timestamp.

Phoenix Table Creation. Error on recreating tables with a Timestamp.

 

I git cloned the following repository of phoenix:https://github.com/apache/phoenix. I am connecting to phoenix via sqlline.pylocalhost:2181. Although, I'm using Phoenix parcel:4.7.0-1-clabs_phoenix1.3.0.p0.000 .jar files instead of the default in the current version.

 

 

I'm using a jar to connect to phoenix, and construct statements to create and drop tables. The following are the .java files.

 

Example command in redhat terminal: ...PhoenixTableTool actionType schemaFile [epoch_timestamp] [versionNum]

 

  1. java -cp target/CommonView-1.0.jar:$CLASS_PATH com.td.commonview.utils.PhoenixTableTool drop resource/Individual
     
    java -cp target/CommonView-1.0.jar:$CLASS_PATH com.td.commonview.utils.PhoenixTableTool create resource/Individual 1490713051 5

This connects to phoenix and constructs a statement to either create or drop a table:

  1. package com.td.commonview.utils;
     
     
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
     
     
     
     
     
     
     
    public class PhoenixTableTool {
     
     
    public static void main(String[] args) {
    // TODO Auto-generated method stub
     
     
    Statement stmt = null;
    if(args.length != 3 && args.length !=2 && args.length !=4){
    System.out.println("This application requires 2 required and 2 optional parameters: actionType schmaFileName [timestamp] [versions]");
    System.exit(1);
    }
    String actionType = args[0];
    String schemaFileName = args[1];
    //This tool supports two actions, either Drop Table or Create table
    System.out.println("Action is: " + actionType);
    System.out.println("SchemaFileName is: " + schemaFileName);
     
     
    Properties props = new Properties();
    if(args.length >= 3 ){
    try {
    Integer.parseInt(args[2]);
    } catch (NumberFormatException e){
    System.exit(1);
    }
    props.setProperty("CurrentSCN", args[2]);
    }
    String versionNum = "2147483647"; // default value, Integer Max
    if(args.length == 4 ){
    try {
    Integer.parseInt(args[3]);
    } catch (NumberFormatException e){
    System.exit(1);
    }
    versionNum = args[3];
    }
    String commandStr="";
    if (actionType.equalsIgnoreCase("create"))
    commandStr = TableSchemaParser.parse(schemaFileName) + " VERSIONS=" +versionNum;
    else if (actionType.equalsIgnoreCase("drop"))
    commandStr = "drop table " + schemaFileName.substring(schemaFileName.lastIndexOf("/") + 1).trim();
    else {
    System.out.println("Invalid actionType: " + actionType);
    System.exit(1);
    }
    Connection con;
    System.out.println("command:" + commandStr);
    String quorum = System.getProperty("phoenix.quorum", "localhost:2181");
    try {
    con = DriverManager.getConnection(
    "jdbc:phoenix:"+quorum, props);
    stmt = con.createStatement();
     
     
    //stmt.executeUpdate("create table if not exists test_new2 (id integer not null primary key, c1 varchar, c2 varchar, c3 varchar)");
    stmt.executeUpdate(commandStr);
    con.commit();
    con.close();
    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }
     
    }
This parses Phoenix table schema for table creation purposes:
  1. package com.td.commonview.utils;
     
     
    import java.io.BufferedReader;
    import java.io.FileReader;
    import java.io.IOException;
     
     
    public class TableSchemaParser {
     
     
    public static String parse(String fileName){
    String fileContent = "";
    BufferedReader br = null;
    //Joiner joiner = Joiner.on(",").skipNulls();
    StringBuilder sb = new StringBuilder("create table ");
    sb.append( fileName.substring(fileName.lastIndexOf("/") + 1).trim() );
    sb.append('(');
    try {
    br = new BufferedReader (new FileReader(fileName));
    String prefix = "";
    String fileLine = "";
    while ((fileLine = br.readLine()) != null) {
    if(!fileLine.trim().isEmpty()){
    sb.append(prefix);
    prefix = ",";
    sb.append(fileLine.replace(':', ' '));
    }
    }
    sb.append(')');
    fileContent = sb.toString();
    br.close();
    } catch (IOException ioe) {
    ioe.getMessage();
    }
    return fileContent;
    }
    }

Example Phoenix table schema:

  1. [cloudera@quickstart resource]$ cat Individual
    id:varchar not null primary key
    fname:varchar
    lname:varchar
    address:varchar
    country_id:integer
    phoneNum:varchar
    email:varchar

ISSUE:

Creating a table with a timestamp for the first time works, but if that table is dropped it cannot be recreated with a timestamp and the same table name without receiving errors.

 

NOTE:

Creating and dropping a table without a timestamp succeeds and it seems to be removed from Hbase and Phoenix.

I believe the issue could be related to this following bug: https://issues.apache.org/jira/browse/PHOENIX-1417

 

When creating a table, dropping, then recreating the same table with a timestamp I am given a NewerTableAlreadyExistsException:
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:java.io.tmpdir=/tmp
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:java.compiler=<NA>
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:os.name=Linux
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:os.arch=amd64
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:os.version=2.6.32-573.el6.x86_64
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:user.name=cloudera
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:user.home=/home/cloudera
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Client environment:user.dir=/home/cloudera
17/08/09 09:59:44 INFO zookeeper.ZooKeeper: Initiating client connection, connectString=localhost:2181 sessionTimeout=60000 watcher=hconnection-0x293a5bf60x0, quorum=localhost:2181, baseZNode=/hbase
17/08/09 09:59:44 INFO zookeeper.ClientCnxn: Opening socket connection to server localhost/127.0.0.1:2181. Will not attempt to authenticate using SASL (unknown error)
17/08/09 09:59:44 INFO zookeeper.ClientCnxn: Socket connection established, initiating session, client: /127.0.0.1:42621, server: localhost/127.0.0.1:2181
17/08/09 09:59:44 INFO zookeeper.ClientCnxn: Session establishment complete on server localhost/127.0.0.1:2181, sessionid = 0x15dc718bf9603d0, negotiated timeout = 60000
17/08/09 09:59:45 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
17/08/09 09:59:45 INFO metrics.Metrics: Initializing metrics system: phoenix
17/08/09 09:59:45 WARN impl.MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-phoenix.properties,hadoop-metrics2.properties
17/08/09 09:59:46 INFO impl.MetricsSystemImpl: Scheduled snapshot period at 10 second(s).
17/08/09 09:59:46 INFO impl.MetricsSystemImpl: phoenix metrics system started
17/08/09 09:59:46 INFO Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available
17/08/09 09:59:50 INFO client.HBaseAdmin: Created INDIVIDUAL
org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=INDIVIDUAL
at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:2136)
at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:828)
at org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:183)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:338)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:326)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:324)
at org.apache.phoenix.jdbc.PhoenixStatement.executeUpdate(PhoenixStatement.java:1326)
at com.td.commonview.utils.PhoenixTableTool.main(PhoenixTableTool.java:78)
 

Notice that it mentions the table was created, yet still gives the error. The table was only available in Hbase, but wasn't visible in Phoenix.

 

Then I tried dropping the table with the same timestamp which also led to a NewerTableAlreadyExistsException:
  1. 17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:java.io.tmpdir=/tmp
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:java.compiler=<NA>
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:os.name=Linux
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:os.arch=amd64
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:os.version=2.6.32-573.el6.x86_64
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:user.name=cloudera
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:user.home=/home/cloudera
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Client environment:user.dir=/home/cloudera
    17/08/09 10:01:04 INFO zookeeper.ZooKeeper: Initiating client connection, connectString=localhost:2181 sessionTimeout=60000 watcher=hconnection-0x130d63be0x0, quorum=localhost:2181, baseZNode=/hbase
    17/08/09 10:01:04 INFO zookeeper.ClientCnxn: Opening socket connection to server localhost/127.0.0.1:2181. Will not attempt to authenticate using SASL (unknown error)
    17/08/09 10:01:04 INFO zookeeper.ClientCnxn: Socket connection established, initiating session, client: /127.0.0.1:42869, server: localhost/127.0.0.1:2181
    17/08/09 10:01:04 INFO zookeeper.ClientCnxn: Session establishment complete on server localhost/127.0.0.1:2181, sessionid = 0x15dc718bf9603d7, negotiated timeout = 60000
    17/08/09 10:01:05 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
    17/08/09 10:01:05 INFO metrics.Metrics: Initializing metrics system: phoenix
    17/08/09 10:01:05 WARN impl.MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-phoenix.properties,hadoop-metrics2.properties
    17/08/09 10:01:06 INFO impl.MetricsSystemImpl: Scheduled snapshot period at 10 second(s).
    17/08/09 10:01:06 INFO impl.MetricsSystemImpl: phoenix metrics system started
    17/08/09 10:01:06 INFO Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available
    org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=INDIVIDUAL
    at org.apache.phoenix.schema.MetaDataClient.dropTable(MetaDataClient.java:2312)
    at org.apache.phoenix.schema.MetaDataClient.dropTable(MetaDataClient.java:2226)
    at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDropTableStatement$1.execute(PhoenixStatement.java:860)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:338)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:326)
    at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:324)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeUpdate(PhoenixStatement.java:1326)
    at com.td.commonview.utils.PhoenixTableTool.main(PhoenixTableTool.java:78)
Dropping without the timestamp resulted in a TableNotDefinedException:
  1. 17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:java.io.tmpdir=/tmp
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:java.compiler=<NA>
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:os.name=Linux
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:os.arch=amd64
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:os.version=2.6.32-573.el6.x86_64
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:user.name=cloudera
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:user.home=/home/cloudera
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Client environment:user.dir=/home/cloudera
    17/08/09 10:03:26 INFO zookeeper.ZooKeeper: Initiating client connection, connectString=localhost:2181 sessionTimeout=60000 watcher=hconnection-0x130d63be0x0, quorum=localhost:2181, baseZNode=/hbase
    17/08/09 10:03:26 INFO zookeeper.ClientCnxn: Opening socket connection to server localhost/127.0.0.1:2181. Will not attempt to authenticate using SASL (unknown error)
    17/08/09 10:03:26 INFO zookeeper.ClientCnxn: Socket connection established, initiating session, client: /127.0.0.1:43307, server: localhost/127.0.0.1:2181
    17/08/09 10:03:26 INFO zookeeper.ClientCnxn: Session establishment complete on server localhost/127.0.0.1:2181, sessionid = 0x15dc718bf9603e2, negotiated timeout = 60000
    17/08/09 10:03:27 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
    17/08/09 10:03:27 INFO metrics.Metrics: Initializing metrics system: phoenix
    17/08/09 10:03:27 WARN impl.MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-phoenix.properties,hadoop-metrics2.properties
    17/08/09 10:03:27 INFO impl.MetricsSystemImpl: Scheduled snapshot period at 10 second(s).
    17/08/09 10:03:27 INFO impl.MetricsSystemImpl: phoenix metrics system started
    17/08/09 10:03:28 INFO Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available
    org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): Table undefined. tableName=INDIVIDUAL
    at org.apache.phoenix.schema.MetaDataClient.dropTable(MetaDataClient.java:2309)
    at org.apache.phoenix.schema.MetaDataClient.dropTable(MetaDataClient.java:2226)
    at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDropTableStatement$1.execute(PhoenixStatement.java:860)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:338)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:326)
    at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:324)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeUpdate(PhoenixStatement.java:1326)
    at com.td.commonview.utils.PhoenixTableTool.main(PhoenixTableTool.java:78)

I tried recreating the table without a timestamp after and it was successful, the table was visible in Hbase and Phoenix. Dropping the table also worked. The timestamp is necessary, which is why this is an issue. I appreciate any suggestions, or fixes!

 
Announcements