'2017/12/19'에 해당되는 글 3건

  1. 2017.12.19 [Phoenix] 시간 관련 API 예제
  2. 2017.12.19 [Phoenix] describe
  3. 2017.12.19 [Phoenix] outputformat 결과 출력 형태 변경 예제




DATE 타입 값에 대해서 where.. between을 사용하려면 TO_TIMESTAMP를 사용한다


where service_tag = 'google_plus' and log_time between TO_TIMESTAMP('2017-12-19 10:00:24.000') and TO_TIMESTAMP('2017-12-19 10:05:24.000') order by timestamp 


https://phoenix.apache.org/language/functions.html#to_timestamp



current_time()과 now()의 결과는 동일하다.


> select current_time();

+---------------------------------+

| TIME '2017-12-19 03:05:53.873'  |

+---------------------------------+

| 2017-12-19 03:05:53.873         |

+---------------------------------+

1 row selected (0.006 seconds)


> select now();

+---------------------------------+

| DATE '2017-12-19 03:05:56.345'  |

+---------------------------------+

| 2017-12-19 03:05:56.345         |

+---------------------------------+






1분전, 2분전 결과를 보고 싶다면 다음 결과를 확인한다.


> select now(), now() - (1.0 / (60 * 24));


+---------------------------------+---------------------------------+

| DATE '2017-12-19 03:10:39.428'  | DATE '2017-12-19 03:09:39.429'  |

+---------------------------------+---------------------------------+

| 2017-12-19 03:10:39.428         | 2017-12-19 03:09:39.429         |

+---------------------------------+---------------------------------+

1 row selected (0.005 seconds)



> select now(), now() - (2.0 / (60 * 24));

+---------------------------------+---------------------------------+

| DATE '2017-12-19 03:10:42.772'  | DATE '2017-12-19 03:08:42.772'  |

+---------------------------------+---------------------------------+

| 2017-12-19 03:10:42.772         | 2017-12-19 03:08:42.772         |

+---------------------------------+---------------------------------+










시간과 날짜 함수는 다음과 같이 지원한다.


Time and Date Functions

TO_DATE
TO_TIME
TO_TIMESTAMP
CURRENT_TIME
CONVERT_TZ
TIMEZONE_OFFSET
NOW
YEAR
MONTH
WEEK
DAYOFYEAR
DAYOFMONTH
DAYOFWEEK
HOUR
MINUTE
SECOND



Posted by '김용환'
,

[Phoenix] describe

hbase 2017. 12. 19. 11:49



Phoenix 테이블 정보를 보려면 다음과 같이 느낌표를 먼저 입력하고 describe를 입력한다. 




> !describe <테이블 이름>

+------------+--------------+------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+-------------+

| TABLE_CAT  | TABLE_SCHEM  |    TABLE_NAME    | COLUMN_NAME  | DATA_TYPE  | TYPE_NAME  | COLUMN_SIZE  | BUFFER_LENGTH  | DECIMAL_DIGITS  | NUM_PREC_RADIX  | NULLABLE  | REMARKS  | COLUMN_DEF  | SQL_DATA_TYPE  | SQL_DATETIME_SUB  | CHAR_OCTET_ |

+------------+--------------+------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+-------------+










모르면 > !help를 입력한다.


!help

!all                Execute the specified SQL against all the current

                    connections

!autocommit         Set autocommit mode on or off

!batch              Start or execute a batch of statements

!brief              Set verbose mode off

!call               Execute a callable statement

!close              Close the current connection to the database

!closeall           Close all current open connections

!columns            List all the columns for the specified table

!commit             Commit the current transaction (if autocommit is off)

!connect            Open a new connection to the database.

!dbinfo             Give metadata information about the database

!describe           Describe a table

!dropall            Drop all tables in the current database

!exportedkeys       List all the exported keys for the specified table

!go                 Select the current connection

!help               Print a summary of command usage

!history            Display the command history

!importedkeys       List all the imported keys for the specified table

!indexes            List all the indexes for the specified table

!isolation          Set the transaction isolation for this connection

!list               List the current connections

!manual             Display the SQLLine manual

!metadata           Obtain metadata information

!nativesql          Show the native SQL for the specified statement

!outputformat       Set the output format for displaying results

                    (table,vertical,csv,tsv,xmlattrs,xmlelements)

!primarykeys        List all the primary keys for the specified table

!procedures         List all the procedures

!properties         Connect to the database specified in the properties file(s)

!quit               Exits the program

!reconnect          Reconnect to the database

!record             Record all output to the specified file

!rehash             Fetch table and column names for command completion

!rollback           Roll back the current transaction (if autocommit is off)

!run                Run a script from the specified file

!save               Save the current variabes and aliases

!scan               Scan for installed JDBC drivers

!script             Start saving a script to a file

!set                Set a sqlline variable


Variable        Value      Description

=============== ========== ================================

Posted by '김용환'
,


아파치 피닉스(Apache Phoenix)에 실시간 로그를 저장해보고 있다.

sqlline에서 로그가 너무 많을 때는 테이블에서 로그가 보이지도 않는다. 


데이터를 보는 뷰가 horizontal, vertical로 되어 있다. 기본 뷰는 horizontal이다. 


로그가 길면 볼 방법이 없다. 그러나 outputformat을 사용하면 유용하다.



> !outputformat vertical 


> select now(), now() - (2.0 / (60 * 24));

DATE '2017-12-19 03:12:39.899'  2017-12-19 03:12:39.899

DATE '2017-12-19 03:10:39.899'  2017-12-19 03:10:39.899






다시 원래 기본 뷰를 사용하려면 다음 커맨드를 실행한다.


> !outputformat table

select now(), now() - (2.0 / (60 * 24));

+---------------------------------+---------------------------------+

| DATE '2017-12-19 03:12:20.209'  | DATE '2017-12-19 03:10:20.209'  |

+---------------------------------+---------------------------------+

| 2017-12-19 03:12:20.209         | 2017-12-19 03:10:20.209         |

+---------------------------------+---------------------------------+




이외 여러 결과 포맷을 사용할 수 있다. 


> !outputformat csv

> select now(), now() - (2.0 / (60 * 24));

'DATE '2017-12-19 05:06:44.272'','DATE '2017-12-19 05:04:44.272''

'2017-12-19 05:06:44.272','2017-12-19 05:04:44.272'




> !outputformat tsv

> select now(), now() - (2.0 / (60 * 24));

'DATE '2017-12-19 05:07:23.258'' 'DATE '2017-12-19 05:05:23.258''

'2017-12-19 05:07:23.258' '2017-12-19 05:05:23.258'



>!outputformat xmlattr

> select now(), now() - (2.0 / (60 * 24));

<resultset>

  <result DATE '2017-12-19 05:09:30.500'="2017-12-19 05:09:30.500" DATE '2017-12-19 05:07:30.500'="2017-12-19 05:07:30.500"/>

</resultset>



> !outputformat xmlelements

> select now(), now() - (2.0 / (60 * 24));

<resultset>

  <result>

    <DATE '2017-12-19 05:09:53.013'>2017-12-19 05:09:53.013</DATE '2017-12-19 05:09:53.013'>

    <DATE '2017-12-19 05:07:53.013'>2017-12-19 05:07:53.013</DATE '2017-12-19 05:07:53.013'>

  </result>

</resultset>

Posted by '김용환'
,