Apache Phoenix create view throws TableAlreadyExistException

Question

I have created a table in Hbase using shell which contains 2 columns families. I am able to write into it using Hbase client API. I am using the phoenix JDBC driver to query data from it. I learnt that there is no one to one mapping between HBase and Phoenix table. So, i created a view for my existing table but whenever i run my code i get

org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=test
at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1911)
at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:744)
at org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:186)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:303)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:295)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:293)
at org.apache.phoenix.jdbc.PhoenixStatement.executeUpdate(PhoenixStatement.java:1236)
at com.lam.app.PhoenixClient.main(PhoenixClient.java:49)

My client code is like this

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PhoenixClient
{

    public static void main(String[] args)
    {
        // Create variables
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        PreparedStatement ps = null;

        try
        {
            // Connect to the database
            connection = DriverManager.getConnection("jdbc:phoenix:zkserver:2181:/hbase-unsecure");

            // Create a JDBC statement
            statement = connection.createStatement();

            //HBase table schema - Table name : test, columnfamily 1 - data, ColumnFamily2 - context 
            // Execute our statements
            statement.executeUpdate("CREATE VIEW \"test\" (pk VARCHAR not null PRIMARY KEY, "
                    + "\"data\".\"mydata\" VARCHAR," + " \"context\".\"mycontext\" VARCHAR)");

            //connection.commit();

            // Query for table
            /*ps = connection
                .prepareStatement("select * from \"test\" WHERE \"mydata\" = \"names\"");
            rs = ps.executeQuery();
            System.out.println("Table Values");
            while (rs.next())
            {
                Integer myKey = rs.getInt(1);
                String myColumn = rs.getString(2);
                System.out.println("\tRow: " + myKey + " = " + myColumn);
            }*/

        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (ps != null)
            {
                try
                {
                    ps.close();
                }
                catch (Exception e)
                {
                }
            }
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (Exception e)
                {
                }
            }
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (Exception e)
                {
                }
            }
            if (connection != null)
            {
                try
                {
                    connection.close();
                }
                catch (Exception e)
                {
                }
            }
        }

    }
}

I can scan table using Hbase shell and can view the data and I am aware of the table name being case sensitive (specially phoenix makes everything upper case) but not sure why I cannot create view. Please help

as the error says, you already have table named "test" in Hbase. give another name — Nirmal Ram
@NirmalRam I am aware that table "test" already exists because I created it using Hbase shell but what I am trying to do is create a view for it using Phoenix so that I can use the query. — AnswerSeeker

AnswerSeeker AnswerSeeker · Accepted Answer · 2016-11-16T09:50:38

Following @rickmoritz suggestion, I dropped the view before creating it and that worked.

statement.executeUpdate("DROP VIEW \"test\"");

Apache Phoenix create view throws TableAlreadyExistException

1 Answers