Cassandra 整合測試

測試資料樣本回首頁

資料單筆範例
 uid                                  | appl_date | appl_type | case_type | drug_fre           | drug_no    | drug_use 
--------------------------------------+-----------+-----------+-----------+--------------------+------------+----------
 46378200-7b3a-11e4-99a9-c3ab0fbb0590 |  20120101 |         1 |        AA | JSELIATVNSBSGIJOAT | A046647100 |   JSELIA 

 fee_ym | hosp_id                            | order_seq_no | order_type | seq_no      | total_amt  | total_qty | unit_price
--------+------------------------------------+--------------+------------+-------------+------------+-----------+-----------
 201201 | AAAACBOUOVHGGAEOHVRVYXLOEDQSCLTUOV |            1 |          J | 5378.000000 | 1.3475e+05 |        37 |       3642
建立資料庫:CQL 建立資料庫:Python
CREATE TABLE emprogria.nhi_oo (
    uid uuid PRIMARY KEY,
    appl_date text,
    appl_type int,
    case_type text,
    drug_fre text,
    drug_no text,
    drug_use text,
    fee_ym text,
    hosp_id text,
    order_seq_no int,
    order_type text,
    seq_no text,
    total_amt float,
    total_qty int,
    unit_price float
)
# -*- coding: utf-8 -*-

import csv
from cassandra.cluster import Cluster

def createTable(dbSession):
#	result = dbSession.execute("DROP TABLE nhi_oo;")
	result = dbSession.execute("CREATE TABLE nhi_oo (						\
		UID uuid PRIMARY KEY, FEE_YM text, APPL_TYPE int, HOSP_ID text,	\
		APPL_DATE text,	CASE_TYPE text, SEQ_NO text, ORDER_TYPE text,	\
		DRUG_NO text, DRUG_USE text, DRUG_FRE text, UNIT_PRICE float,	\
		TOTAL_QTY int, TOTAL_AMT float, ORDER_SEQ_NO int);")

def insertTable(dbSession):
	result = dbSession.execute('TRUNCATE nhi_oo;')
	dbFilePath = 'C:/Java/apache-cassandra-2.1.2/Script/NHI-OO-100.csv'

	with open(dbFilePath, 'rb') as dbfile:
		dbReader = csv.DictReader(dbfile, delimiter=',', quotechar='"')
		sqlTemplate = "INSERT INTO nhi_oo (UID, FEE_YM, APPL_TYPE, HOSP_ID, APPL_DATE,	\
				CASE_TYPE, SEQ_NO, ORDER_TYPE, DRUG_NO, DRUG_USE, DRUG_FRE,	\
				UNIT_PRICE, TOTAL_QTY, TOTAL_AMT, ORDER_SEQ_NO)			\
				VALUES (now(), '%s', %d, '%s', '%s', '%s', '%s', '%s', '%s',	\
                                                '%s', '%s', %f, %d, %f, %d);"
		
		for dbRow in dbReader:
			print '%s\t%s' % (dbRow['HOSP_ID'], dbRow['APPL_DATE'])
			sql = sqlTemplate % (dbRow['FEE_YM'], int(dbRow['APPL_TYPE']), dbRow['HOSP_ID'],		\
				dbRow['APPL_DATE'], dbRow['CASE_TYPE'], dbRow['SEQ_NO'], dbRow['ORDER_TYPE'],	\
				dbRow['DRUG_NO'], dbRow['DRUG_USE'], dbRow['DRUG_FRE'],				\
				float(dbRow['UNIT_PRICE']), int(float(dbRow['TOTAL_QTY'])),			\ 
                                float(dbRow['TOTAL_AMT']), int(float(dbRow['ORDER_SEQ_NO'])))

			result = dbSession.execute(sql)

		dbfile.close()		

dbCluster = Cluster()
dbSession = dbCluster.connect('emprogria')

createTable(dbSession)

讀取外部資料轉入資料庫
# -*- coding: utf-8 -*-

import csv
from cassandra.cluster import Cluster

def insertTable(dbSession):
	result = dbSession.execute('TRUNCATE nhi_oo;')
	dbFilePath = 'C:/Java/apache-cassandra-2.1.2/Script/NHI-OO-100.csv'

	with open(dbFilePath, 'rb') as dbfile:
		dbReader = csv.DictReader(dbfile, delimiter=',', quotechar='"')
		sqlTemplate = "INSERT INTO nhi_oo (UID, FEE_YM, APPL_TYPE, HOSP_ID, APPL_DATE,	\
						CASE_TYPE, SEQ_NO, ORDER_TYPE, DRUG_NO, DRUG_USE, DRUG_FRE,	\
						UNIT_PRICE, TOTAL_QTY, TOTAL_AMT, ORDER_SEQ_NO)			\
						VALUES (now(), '%s', %d, '%s', '%s', '%s', '%s',			\
                                                               '%s', '%s', '%s', '%s', %f, %d, %f, %d);"
		
		for dbRow in dbReader:
			print '%s\t%s' % (dbRow['HOSP_ID'], dbRow['APPL_DATE'])
			sql = sqlTemplate % (dbRow['FEE_YM'], int(dbRow['APPL_TYPE']), dbRow['HOSP_ID'],	\
				dbRow['APPL_DATE'], dbRow['CASE_TYPE'], dbRow['SEQ_NO'], dbRow['ORDER_TYPE'],	\
				dbRow['DRUG_NO'], dbRow['DRUG_USE'], dbRow['DRUG_FRE'],	\
				float(dbRow['UNIT_PRICE']),	\
                                int(float(dbRow['TOTAL_QTY'])),	\
                                float(dbRow['TOTAL_AMT']),	\
                                int(float(dbRow['ORDER_SEQ_NO'])))

			result = dbSession.execute(sql)

		dbfile.close()		

dbCluster = Cluster()
dbSession = dbCluster.connect('emprogria')

insertTable(dbSession)
讀取資料庫
# -*- coding: utf-8 -*-

import csv
from cassandra.cluster import Cluster

def queryTable(dbSession, sql):
	result = dbSession.execute(sql)
	for dbValues in result:
		print '\t'.join(str(x) for x in dbValues)

dbCluster = Cluster()
dbSession = dbCluster.connect('emprogria')

sql = 'SELECT HOSP_ID, FEE_YM, TOTAL_AMT FROM nhi_oo'
queryTable(dbSession, sql)

讀取資料庫:排序輸出
# -*- coding: utf-8 -*-

import csv
import collections
from cassandra.cluster import Cluster

def queryTableSorted(dbSession, sql):
	result = dbSession.execute(sql)
	memResult = {}
	
	for dbValues in result:
		memResult[dbValues[0]+'-'+dbValues[1]] = dbValues[2]
		
	for dbKey in sorted(memResult):
		print "%s\t%12.0f" % (dbKey, memResult[dbKey])
	
dbCluster = Cluster()
dbSession = dbCluster.connect('emprogria')

sql = 'SELECT HOSP_ID, FEE_YM, TOTAL_AMT FROM nhi_oo'
queryTableSorted(dbSession, sql)

ċ
NHI-OO-0.py
(2k)
李智,
2014年12月3日 下午10:09
ċ
NHI-OO-100.csv
(17k)
李智,
2014年12月3日 下午8:13
ċ
NHI-OO.cql
(0k)
李智,
2014年12月3日 下午7:57