Phoenix sqlline cannot display all columns of table on terminal

Question

Use phoenix sqlline to connect the hbase. On SecureCRT terminal I can only see three columns of table which has more than 10 columns. I would like to display all columns of the table to test if data is ok. Is there any configuration should be set?

0: jdbc:phoenix:10.35.66.72:2181:/hbase> select * from WL.MSGCENTER_PUSHMESSAGE;
+--------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------+
|    PLANID    | BATCHID |                                                                                                                                                 |
+--------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------+
| 520          | 1       | C285995F-AB23-4CF0-A9A4-F29175E9CD36                                                                                                           |
+--------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row selected (0.805 seconds)

Hypophosphite

Answer 1

You can change the output format from horizontal to vertical

!outputformat vertical

Switzer

Agreed, this is very useful and should be the accepted answer. It serializes the table, which is often what a JSON API would do. – Delmadelmar

Answer 2

Try setting the terminal width before starting sqlline

stty cols 200

Rothman

Answer 3

Sqlline is not smart enough to adjust column width. Make the terminal wider and you might see the data.
Ideally, i would recommend you to use squirrel-sql or db-visualizer to connect to Phoenix. They are much better tool to query Phoenix.

Have a look at this: http://search-hadoop.com/m/9UY0h2sGBoSz1Mta1

Circumscissile

Answer 4

I suggest using Jupyter to connect HBase.

Our team use this approach and I can easily view all the columns with a horizontal scroll in Juypter.

Here's the code snippet I use:

import phoenixdb
from sqlalchemy import create_engine
import pandas as pd

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_colwidth', None)


def hb_query(x):
    conn = phoenixdb.connect(url='http://xxxxx:1234')
    with conn.cursor() as cursor:
        cursor.execute(x)
        data = cursor.fetchall()
        des = cursor.description
    conn.close()
    columns = [i.name.lower() for i in des]
    df_tmp = pd.DataFrame(data, columns=columns)
    
    return df_tmp

df = hb_query("""
SELECT *
FROM TABLE_ABCD
""")

df    #view rows here

FYI, you might need to set up Jupyter first which I don't know how and whether it's difficult to set up. My leader set up the environment long before.

Manservant