테스트 환경
SQREAM :
OS: Centos 7.9
SQREAM Version: v2021.2.1.23_test
Client :
OS: Ubuntu 18.04.6 LTS
Dev : JupyterHub + Python 3.9.13 + nodejs 10.24.1 + pip 22.2.2
SQREAM : pysqream 3.1.8 + pysqream-sqlalchemy 0.6
테스트 요약
pysqream을 사용하기 위해서는 python의 버전이 3.6.5 보다 높아야함(이하의 버전에선 pysqream 3.x가 설치되지 않음)
pysqream 3.x 이상을 사용해야함(pysqream 2.x는 지원이 중단되었고 sqlalchemy 0.6을 사용하기 위해선 pysqream 3.x가 필요로함)
Zepplen은 UI상에서 사용자별 연결을 관리할수 있으나(JDBC) jupyter는 그러한 기능을 제공하지 않음
따라서 별도의 모듈(.py)를 만들거나 사용자별 Profile을 통해 간접적으로 제한할 수는 있음
단 사용자가 다른 사용자의 모듈에 접근이 가능하므로 강제적인 제한은 불가
참고URL :
테스트 상세
사용자 접속용 모듈을 생성
고객사 jupyter 환경에 pysqream 3.x pysqream-sqlalchemy 0.6을 설치
SQREAM Connect 전용 Python 파일 생성root@master:/tmp# vi tmp/sqream_python.pyimport osimport pysqreamdef SqreamConn(dbname): myId=str(os.getcwd()).split('/')[-1].split('-')[-1]con=pysqream.connect(host='xxx.xxx.xxx.207', port=3108, database=dbname, username=myId, password=myId, clustered=True)return con
def SqreamConn_lyj():myId=str(os.getcwd()).split('/')[-1].split('-')[-1]con=pysqream.connect(host='xxx.xxx.xxx.207', port=3108, database='lyj', username=myId, password=myId, clustered=True)return con
def SqreamConn_admin():myId=str(os.getcwd()).split('/')[-1].split('-')[-1]con=pysqream.connect(host='xxx.xxx.xxx.207', port=3108, database='lyj', username='sqream', password='sqream', clustered=True)return con
Jupyter User Config상에서 SQREAM Connect Python 파일을 Kernel 시작시 실행하게 함root@master:/home/jupyter-sqream/.ipython/profile_default# vi /home/jupyter-sqream/.ipython/profile_default/ipython_config.py$ c.InteractiveShellApp.exec_files = ['/tmp/sqream_python.py']
아래와 같은 코드를 사용시 정상적으로 작동함
#Define Username,Passwrd=SQREAM, Database=lyj
con1=SqreamConn_admin()
cur1=con1.cursor()
cur1.execute('select show_version()')
result1=cur1.fetchall()
print(result1[0][0])
#Define Username,Password=root, Database=lyj
con2=SqreamConn('lyj')
cur2=con2.cursor()
cur2.execute('select show_version()')
result2=cur2.fetchall()
print(result2[0][0])
#Define Username
con3=SqreamConn_lyj()
cur3=con3.cursor()
cur3.execute('select show_version()')
result3=cur3.fetchall()
print(result3[0][0])
pysqream 을 직접 수정하는 방법
코드상에 Service 명시 후 Python에서 Service를 명시하여 호출시
$ /data/anaconda3/lib/python3.9/site-packages/pysqream/pysqream.py
$ con = pysqream.connect(host='xxx.xxx.xxx.207',database='lyj', port=3108, username='sqream', password='sqream', clustered=True,service='v100')
==> xxx.xxx._5001/sqream_20220818_000.log <==
#SQ#|1660811483978793304|2022-08-18 17:31:23.978|INFO|0x00007f5a297d6700:c229|xxx.xxx.xxx.xxx|5001|229|lyj|sqream|248|v100|2|"Reconstruct query before parsing"|#EOM#
#SQ#|1660811483980046656|2022-08-18 17:31:23.980|INFO|0x00007f5a297d6700:c229|xxx.xxx.xxx.xxx|5001|229|lyj|sqream|248|v100|2|"select * from case04_1"|#EOM#
#SQ#|1660811483986326056|2022-08-18 17:31:23.986|INFO|0x00007f5a297d6700:c229s248|xxx.xxx.xxx.xxx|5001|229|lyj|sqream|248|v100|4|"Statement execution"|#EOM#
#SQ#|1660811484032534859|2022-08-18 17:31:24.32|INFO|0x00007f5a297d6700:c229s248|xxx.xxx.xxx.xxx|5001|229|lyj|sqream|248|v100|10|"Success"|#EOM#
#SQ#|1660811484032629825|2022-08-18 17:31:24.32|INFO|0x00007f5a297d6700:c229s248|xxx.xxx.xxx.xxx|5001|229|lyj|sqream|248|v100|31|"10"|#EOM#
#SQ#|1660811484032664266|2022-08-18 17:31:24.32|INFO|0x00007f5a297d6700:c229s248|xxx.xxx.xxx.xxx|5001|229|lyj|sqream|248|v100|32|"10"|#EOM#
코드상에 Service 명시 후 Python에서 Service를 명시하지 않고 호출시
def connect_database(self, database, username, password, service='etl'):
def connect(host, port, database, username, password, clustered = False, use_ssl = False, service='etl', log=False, reconnect_attempts=3, reconnect_interval=10):
==> xxx.xxx.xxx.xxx_5001/sqream_20220818_000.log <==
#SQ#|1660811587453345216|2022-08-18 17:33:07.453|INFO|0x00007f5a297d6700:connMsgHandler|xxx.xxx.xxx.xxx|5001|-1|lyj|sqream|-1|etl|101|"Login Success"|#EOM#
#SQ#|1660811587455486469|2022-08-18 17:33:07.455|INFO|0x00007f5a297d6700:c244|xxx.xxx.xxx.xxx|5001|244|lyj|sqream|262|etl|2|"Reconstruct query before parsing"|#EOM#
#SQ#|1660811587457241113|2022-08-18 17:33:07.457|INFO|0x00007f5a297d6700:c244|xxx.xxx.xxx.xxx|5001|244|lyj|sqream|262|etl|2|"select * from case04_1"|#EOM#
#SQ#|1660811587465293679|2022-08-18 17:33:07.465|INFO|0x00007f5a297d6700:c244s262|xxx.xxx.xxx.xxx|5001|244|lyj|sqream|262|etl|4|"Statement execution"|#EOM#
#SQ#|1660811587509712401|2022-08-18 17:33:07.509|INFO|0x00007f5a297d6700:c244s262|xxx.xxx.xxx.xxx|5001|244|lyj|sqream|262|etl|10|"Success"|#EOM#
#SQ#|1660811587509806935|2022-08-18 17:33:07.509|INFO|0x00007f5a297d6700:c244s262|xxx.xxx.xxx.xxx|5001|244|lyj|sqream|262|etl|31|"10"|#EOM#
#SQ#|1660811587509841853|2022-08-18 17:33:07.509|INFO|0x00007f5a297d6700:c244s262|xxx.xxx.xxx.xxx|5001|244|lyj|sqream|262|etl|32|"10"|#EOM#
