def t1_allow_access(self, cluster_id='my-cl1'):
StopWatch.start("allow access")
m = Provider()
r = m.allow_access(cluster_id)
StopWatch.stop("allow access")
print(r)
assert True
def t1_rename_cluster(self, cluster_id='my-cl1', new_id='my=cl111'):
StopWatch.start("rename cluster")
m = Provider()
r = m.rename_cluster(cluster_id, new_id)
StopWatch.stop("rename cluster")
print(r)
assert True
def describe_clusters():
redshift = Provider()
result = redshift.describe_clusters({})
print(result)
return result
def t1_describe_clusters(self):
StopWatch.start("describe clusters")
m = Provider()
r = m.describe_clusters()
StopWatch.stop("describe clusters")
print(r)
assert True
def change_node_type(cluster_id, clusterType, nodeType):
redshift = Provider()
result = redshift.resize_cluster_node_types({'CLUSTER_ID': cluster_id,
'nodetype': nodeType,
'CLUSTER_TYPE': clusterType})
print(result)
return result
def t1_delete_cluster(self, cluster_id='my-cl1'):
StopWatch.start("delete cluster")
m = Provider()
r = m.delete_cluster(cluster_id)
StopWatch.stop("delete cluster")
print(r)
assert True
def describe_cluster(cluster_Id):
redshift = Provider()
result = redshift.describe_cluster({'CLUSTER_ID': cluster_Id})
print(result)
return result
def resize_cluster(cluster_id, clusterType, nodeCount, nodeType):
redshift = Provider()
result = redshift.resize_cluster_node_count({'CLUSTER_ID': cluster_id,
'nodetype': nodeType,
'CLUSTER_TYPE': clusterType,
'nodes': int(nodeCount)})
print(result)
return result
def chg_password(cluster_id, newPass):
redshift = Provider()
result = redshift.modify_cluster({
'CLUSTER_ID': cluster_id,
'newpass': newPass
})
print(result)
return result
def rename_cluster(cluster_id, newId):
redshift = Provider()
result = redshift.rename_cluster({
'CLUSTER_ID': cluster_id,
'newid': newId
})
print(result)
return result
def t1_modify_cluster(self,
cluster_id='my-cl1',
new_pass='******'):
StopWatch.start("modify cluster")
m = Provider()
r = m.modify_cluster(cluster_id, new_pass)
StopWatch.stop("modify cluster")
print(r)
assert True
def t1_resize_cluster_node_types(self,
cluster_id='my-cl1',
node_type='ds2.xlarge',
node_count=2):
StopWatch.start("resize cluster node types")
m = Provider()
r = m.resize_cluster_node_types(cluster_id, node_type, node_count)
StopWatch.stop("resize cluster node types")
print(r)
assert True
def t1_resize_cluster_to_multi_node(self,
cluster_id='my-cl1',
cluster_type='multi-node',
node_count=2,
node_type='dc2.large'):
StopWatch.start("resize cluster to multi node")
m = Provider()
r = m.resize_cluster_to_multi_node(cluster_id, cluster_type,
node_count, node_type)
StopWatch.stop("resize cluster to multi node")
print(r)
assert True
def t1_create_single_node_cluster(self,
db_name='db1',
cluster_id='my-cl1',
cluster_type='single-node',
node_type='dc2.large',
user_name='awsuser',
passwd='AWSPass321'):
StopWatch.start("create single node cluster")
m = Provider()
r = m.create_single_node_cluster(db_name, cluster_id, cluster_type,
node_type, user_name, passwd)
StopWatch.stop("create single node cluster")
print(r)
assert True
def create_multi_node_cluster(cluster_id, dbName, masterUserName, passWord,
nodeType, clusterType, nodeCount):
redshift = Provider()
result = redshift.create_multi_node_cluster({
'CLUSTER_ID': cluster_id,
'DB_NAME': dbName,
'USER_NAME': masterUserName,
'PASSWD': passWord,
'nodetype': nodeType,
'CLUSTER_TYPE': clusterType,
'nodes': int(nodeCount)
})
print(result)
return result
def t1_run_select_query(
self,
cluster_id='my-cl1',
db_name='db1',
host='my-cl1.ced9iqbk50ks.us-west-2.redshift.amazonaws.com',
port=5439,
user_name='awsuser',
passwd='AWSPass321',
query_text="SELECT COUNT(*) FROM emp"):
StopWatch.start("run select")
m = Provider()
r = m.runselectquery_text(cluster_id, db_name, host, port, user_name,
passwd, query_text)
StopWatch.stop("run select")
print(r)
assert True
def t1_run_dml(self,
cluster_id='my-cl1',
db_name='db1',
host='my-cl1.ced9iqbk50ks.us-west-2.redshift.amazonaws.com',
port=5439,
user_name='awsuser',
passwd='AWSPass321',
dml_file='./redshiftdmlfile.sql'):
fd = open(dml_file, 'r')
dml_file_contents = fd.read()
fd.close()
b64_sql_file_contents = base64.b64encode(
bytes(dml_file_contents, 'ascii'))
StopWatch.start("run dml")
m = Provider()
r = m.rundml(cluster_id, db_name, host, port, user_name, passwd,
b64_sql_file_contents)
StopWatch.stop("run dml")
print(r)
assert True
def test_resize_cluster_node_count(self):
m = Provider()
# print(m.resize_cluster_node_count(args={'CLUSTER_ID':'cl1','type':'multi-node','nodes':3}))
pass
def do_redshift(self, args, arguments):
"""
::
Usage:
redshift describe CLUSTER_ID
redshift create CLUSTER_ID DB_NAME USER_NAME PASSWD --nodetype=NODE_TYPE [--type=TYPE] [--nodes=NODE_COUNT]
redshift resize CLUSTER_ID [--type=TYPE] [--nodes=NODE_COUNT] [--nodetype=NODE_TYPE]
redshift modify CLUSTER_ID [--newid=NEW_CLUSTER_ID] [--newpass=NEW_PASSWD]
redshift delete CLUSTER_ID
This command is used to interface with Amazon Web Services
RedShift service to create a single-node, or multi-node cluster,
resize, modify, and delete a database cluster
Arguments:
CLUSTER_ID The AWS RedShift Cluster ID.
DB_NAME The name of the database
USER_NAME The user name for the master user
PASSWD The password of the master user
Options:
--type=TYPE The type of the cluster - single-node, or multi-node. [default: single-node]
--nodetype=NODE_TYPE The type of the clustered nodes [default: dc2.large]
--nodes=NODE_COUNT The number of nodes in the cluster. [default: 1]
--newid=NEW_CLUSTER_ID The new ID of the cluster
--newpass=NEW_PASSWD The new password for the master user.
Description:
redshift describe CLUSTER_ID
Gives a detailed description of the redshift cluster
redshift create CLUSTER_ID DB_NAME USER_NAME PASSWD NODE_TYPE [--type=TYPE] [--nodes=NODE_COUNT]
Creates the redshift cluster, either single-node, or multi-node, with the given DB name, master
username, password of the master user, with the node instance type, and count of nodes
redshift resize CLUSTER_ID [--type=TYPE] [--nodes=NODE_COUNT]
Resizes the cluster to the specified type, and to the count of nodes specified
redshift modify CLUSTER_ID [--newid=NEW_CLUSTER_ID] [--newpass=NEW_PASSWD]
Modifies the cluster by changing the cluster id or changing the password of the master user
redshift delete CLUSTER_ID
Delete the cluster
Now that we have a redhift cluster we can interface with it
FUNCTIONALITY TBD
"""
map_parameters(arguments, 'type', 'nodetype', 'nodes', 'newid',
'newpass')
redshift = Provider()
# {'describe': False, 'CLUSTER_ID': 'cl13',
# 'create': False, 'DB_NAME': None, 'USER_NAME': None, 'PASSWD': None, '--nodetype': 'dc1.large',
# '--type': 'single-node', '--nodes': '1',
# 'resize': False, 'modify': True, '--newid': 'cl14', '--newpass': None,
# 'delete': False,
# 'type': 'single-node', 'nodetype': 'dc1.large', 'nodes': '1', 'newid': 'cl14', 'newpass': None}
#
print(arguments)
# print(args)
if arguments.describe:
if arguments.get('CLUSTER_ID') is None:
try:
result = redshift.describe_clusters()
print(result)
finally:
return "Unhandled error"
else:
try:
result = redshift.describe_cluster(arguments)
print(result)
finally:
return "Unhandled error"
elif arguments.create:
if arguments.get("type") == 'single-node':
try:
d1 = {
'CLUSTER_ID': arguments.get('CLUSTER_ID'),
'DB_NAME': arguments.get('DB_NAME'),
'nodetype': arguments.get('nodetype'),
'USER_NAME': arguments.get('USER_NAME'),
'PASSWD': arguments.get('PASSWD')
}
result = redshift.create_single_node_cluster(d1)
print(result)
finally:
return "Unhandled error"
else:
try:
d1 = {
'CLUSTER_ID': arguments.get('CLUSTER_ID'),
'DB_NAME': arguments.get('DB_NAME'),
'nodetype': arguments.get('nodetype'),
'USER_NAME': arguments.get('USER_NAME'),
'PASSWD': arguments.get('PASSWD'),
'nodes': arguments.get('nodes')
}
result = redshift.create_multi_node_cluster(d1)
print(result)
finally:
return "Unhandled error"
elif arguments.resize:
print("in resize")
print(arguments)
if arguments.get('nodetype') != 'dc2.large':
# resizing nodes
# NOTE: Necessary to have right number of nodes to be passed in
d1 = {
'CLUSTER_ID': arguments.get('CLUSTER_ID'),
'type': arguments.get('type'),
'nodetype': arguments.get('nodetype'),
'nodes': arguments.get('nodes')
}
# print(d1)
result = redshift.resize_cluster_node_types(d1)
print(result)
elif int(arguments.get('nodes')) > 1:
# changing type of cluster from single-node to multi-node
d1 = {
'CLUSTER_ID': arguments.get('CLUSTER_ID'),
'type': 'multi-node',
'nodetype': arguments.get('nodetype'),
'nodes': arguments.get('nodes')
}
# print(d1)
result = redshift.resize_cluster_to_multi_node(d1)
print(result)
else:
return "Argument error"
elif arguments.modify:
if arguments.get('newid') is None and arguments.get('newpass'):
print("in modify")
try:
d1 = {
'CLUSTER_ID': arguments.get('CLUSTER_ID'),
'newpass': arguments.get('newpass')
}
result = redshift.modify_cluster(d1)
print(result)
finally:
return "Unhandled error"
elif arguments.get('newid') and arguments.get('newpass') is None:
print("in rename")
try:
d1 = {
'CLUSTER_ID': arguments.get('CLUSTER_ID'),
'newid': arguments.get('newid')
}
result = redshift.rename_cluster(d1)
print(result)
finally:
return "Unhandled error"
elif arguments.delete:
try:
result = redshift.delete_cluster(arguments)
print(result)
finally:
return "Unhandled error"
else:
print(self.get_options())
def delete_cluster(cluster_id):
redshift = Provider()
result = redshift.delete_cluster({'CLUSTER_ID': cluster_id})
print(result)
return result
def test_describe_clusters(self):
m = Provider()
print(m.describe_clusters(args={}))
def test_describe_cluster(self):
m = Provider()
print(m.describe_cluster(args={'CLUSTER_ID': 'cl2'}))
def test_create_multi_node_cluster(self):
m = Provider()
# print(m.create_multi_node_cluster(args={'CLUSTER_ID': 'my-cl2', 'DB_NAME': 'db1', 'nodetype': 'dc2.large', 'USER_NAME': 'awsuser', 'PASSWD': 'AWSPassword321', 'nodes': '2'}))
pass
def test_delete_cluster(self):
m = Provider()
# m.delete_cluster(args={'CLUSTER_ID':'cl1'})
pass
def test_resize_cluster_node_types(self):
m = Provider()
# print(m.resize_cluster_node_types(args={'CLUSTER_ID':'my-cl3','nodetype':'ds2.xlarge','nodes':2}))
pass
def do_redshift(self, args, arguments):
"""
::
Usage:
redshift describe [CLUSTER_ID]
redshift create CLUSTER_ID DB_NAME USER_NAME PASSWD --nodetype=NODE_TYPE [--type=TYPE] [--nodes=NODE_COUNT]
redshift resize CLUSTER_ID [--type=TYPE] [--nodes=NODE_COUNT] [--nodetype=NODE_TYPE]
redshift modify CLUSTER_ID [--newid=NEW_CLUSTER_ID] [--newpass=NEW_PASSWD]
redshift delete CLUSTER_ID
redshift allowaccess CLUSTER_ID
redshift demoschema DB_NAME USER_NAME PASSWD HOST PORT [--createschema | --deleteschema]
redshift runddl DB_NAME USER_NAME PASSWD HOST PORT --ddlfile=FILE_NAME
redshift rundml DB_NAME USER_NAME PASSWD HOST PORT --dmlfile=FILE_NAME
redshift runquery DB_NAME USER_NAME PASSWD HOST PORT [--empcount] [--querytext=QUERY_TEXT]
This command is used to interface with Amazon Web Services
RedShift service to create a single-node, or multi-node cluster,
resize, modify, and delete a database cluster
Arguments:
CLUSTER_ID The AWS RedShift Cluster ID.
DB_NAME The name of the database
USER_NAME The user name for the master user
PASSWD The password of the master user
QUERY_TEXT The text of the query to execute
FILE_NAME The file containing the queries
Options:
--type=TYPE The type of the cluster - single-node, or multi-node. [default: single-node]
--nodetype=NODE_TYPE The type of the clustered nodes [default: dc2.large]
--nodes=NODE_COUNT The number of nodes in the cluster. [default: 1]
--newid=NEW_CLUSTER_ID The new ID of the cluster
--newpass=NEW_PASSWD The new password for the master user.
--createschema Create the demo schema
--deleteschema Delete the demo schema
--ddlfile=FILE_NAME Specify the DDL (CREATE TABLE, ALTER TABLE, DROP TABLE) to run in the file
--dmlfile=FILE_NAME Specify the DML statements (INSERT, UPDATE, DELETE) to run in the file
--empcount Run the count query on employees (demo schema only)
--querytext=QUERY_TEXT Specifies the text of the query to run
Description:
redshift describe CLUSTER_ID
Gives a detailed description of the redshift cluster. If the CLUSTER_ID is not specified,
all clusters will be listed
redshift create CLUSTER_ID DB_NAME USER_NAME PASSWD NODE_TYPE [--type=TYPE] [--nodes=NODE_COUNT]
Creates the redshift cluster, either single-node, or multi-node, with the given DB name, master
username, password of the master user, with the node instance type, and count of nodes
redshift resize CLUSTER_ID [--type=TYPE] [--nodes=NODE_COUNT]
Resizes the cluster to the specified type, and to the count of nodes specified
redshift modify CLUSTER_ID [--newid=NEW_CLUSTER_ID] [--newpass=NEW_PASSWD]
Modifies the cluster by changing the cluster id or changing the password of the master user
redshift delete CLUSTER_ID
Delete the cluster
Now that we have a redshift cluster, to interface with it, we need to allow access.
redshift allowaccess CLUSTER_ID
Configure the cluster for external (For eg. Python) access. This step is a pre-requisite to running
any queries on the database.
We can now interface with the redshift cluster.
redshift demoschema DB_NAME USER_NAME PASSWD HOST PORT [--createschema | --deleteschema]
Create the demo EMPLOYEE schema
redshift runddl DB_NAME USER_NAME PASSWD HOST PORT --ddlfile=FILE_NAME
Run the DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE) specified in the file
redshift rundml DB_NAME USER_NAME PASSWD HOST PORT --dmlfile=FILE_NAME
Run the DML statements (INSERT, UPDATE, DELETE) specified in the file
redshift runquery DB_NAME USER_NAME PASSWD HOST PORT [--empcount] [--querytext=QUERY_TEXT]
Run the canned query, or the specified SQL
"""
map_parameters(arguments, 'type', 'nodetype', 'nodes', 'newid',
'newpass', 'createschema', 'deleteschema', 'empcount',
'querytext', 'ddlfile', 'dmlfile')
redshift = Provider()
# {'describe': False, 'CLUSTER_ID': 'cl13',
# 'create': False, 'DB_NAME': None, 'USER_NAME': None, 'PASSWD': None, '--nodetype': 'dc1.large',
# '--type': 'single-node', '--nodes': '1',
# 'resize': False, 'modify': True, '--newid': 'cl14', '--newpass': None,
# 'delete': False,
# 'type': 'single-node', 'nodetype': 'dc1.large', 'nodes': '1', 'newid': 'cl14', 'newpass': None}
#
print(arguments)
# print(args)
if arguments.describe:
cluster_id = arguments.get('CLUSTER_ID')
if cluster_id is None:
print("Blank cluster id")
try:
result = redshift.describe_clusters()
print(result)
finally:
return "Unhandled error"
else:
try:
result = redshift.describe_cluster(cluster_id)
print(result)
finally:
return "Unhandled error"
elif arguments.create:
if arguments.get("type") == 'single-node':
try:
db_name = arguments.get('DB_NAME')
cluster_id = arguments.get('CLUSTER_ID')
cluster_type = arguments.get("type")
node_type = arguments.get('nodetype')
user_name = arguments.get('USER_NAME')
passwd = arguments.get('PASSWD')
node_count = int(arguments.get('nodes'))
result = redshift.create_single_node_cluster(
db_name, cluster_id, cluster_type, node_type,
user_name, passwd)
print(result)
finally:
return "Unhandled error"
else:
try:
db_name = arguments.get('DB_NAME')
cluster_id = arguments.get('CLUSTER_ID')
cluster_type = arguments.get("type")
node_type = arguments.get('nodetype')
user_name = arguments.get('USER_NAME')
passwd = arguments.get('PASSWD')
node_count = int(arguments.get('nodes'))
result = redshift.create_multi_node_cluster(
db_name, cluster_id, cluster_type, node_type,
user_name, passwd, node_count)
print(result)
finally:
return "Unhandled error"
elif arguments.resize:
print("in resize")
print(arguments)
node_type = arguments.get('nodetype')
cluster_id = arguments.get('CLUSTER_ID')
cluster_type = arguments.get("type")
node_count = int(arguments.get('nodes'))
if node_type != 'dc2.large':
# resizing nodes
# NOTE: Necessary to have right number of nodes to be passed in
result = redshift.resize_cluster_node_types(
cluster_id, cluster_type, node_count)
print(result)
elif node_count > 1:
# changing type of cluster from single-node to multi-node
cluster_type = 'multi-node'
# print(d1)
result = redshift.resize_cluster_to_multi_node(
cluster_id, cluster_type, node_count, node_type)
print(result)
else:
return "Argument error"
elif arguments.modify:
new_id = arguments.get('newid')
new_pass = arguments.get('newpass')
cluster_id = arguments.get('CLUSTER_ID')
if new_id is None and new_pass:
print("in modify")
try:
result = redshift.modify_cluster(cluster_id, new_pass)
print(result)
finally:
return "Unhandled error"
elif new_id and new_pass is None:
print("in rename")
try:
result = redshift.rename_cluster(cluster_id, new_id)
print(result)
finally:
return "Unhandled error"
elif arguments.delete:
cluster_id = arguments.get('CLUSTER_ID')
try:
result = redshift.delete_cluster(cluster_id)
print(result)
finally:
return "Unhandled error"
elif arguments.allowaccess:
cluster_id = arguments.get('CLUSTER_ID')
try:
result = redshift.allow_access(cluster_id)
print(result)
finally:
return "Unhandled error"
elif arguments.demoschema:
db_name = arguments.get('DB_NAME')
user_name = arguments.get('USER_NAME')
passwd = arguments.get('PASSWD')
host = arguments.get('HOST')
port = arguments.get('PORT')
if port is None:
port = 5439
host_list = host.split('.')
cluster_id = host_list[0]
print("in demoschema")
try:
if arguments.get('createschema'):
result = redshift.create_demo_schema(
cluster_id, db_name, host, port, user_name, passwd)
elif arguments.get('deleteschema'):
result = redshift.delete_demo_schema(
cluster_id, db_name, host, port, user_name, passwd)
print(result)
finally:
return "Unhandled error"
elif arguments.runddl:
print("In run DDL")
db_name = arguments.get('DB_NAME')
user_name = arguments.get('USER_NAME')
passwd = arguments.get('PASSWD')
host = arguments.get('HOST')
port = arguments.get('PORT')
if port is None:
port = 5439
ddl_file_name = arguments.get('ddlfile')
fd = open(ddl_file_name, 'r')
sql_file_contents = fd.read()
fd.close()
b64_sql_file_contents = base64.b64encode(
bytes(sql_file_contents, 'ascii'))
host_list = host.split('.')
cluster_id = host_list[0]
try:
print("in run ddl")
result = redshift.runddl(cluster_id, db_name, host, port,
user_name, passwd,
b64_sql_file_contents)
finally:
return "Unhandled error"
elif arguments.rundml:
print("In run DML")
db_name = arguments.get('DB_NAME')
user_name = arguments.get('USER_NAME')
passwd = arguments.get('PASSWD')
host = arguments.get('HOST')
port = arguments.get('PORT')
if port is None:
port = 5439
dml_file_name = arguments.get('dmlfile')
fd = open(dml_file_name, 'r')
sql_file_contents = fd.read()
fd.close()
b64_sql_file_contents = base64.b64encode(
bytes(sql_file_contents, 'utf-8'))
host_list = host.split('.')
cluster_id = host_list[0]
try:
print("in run dml")
result = redshift.rundml(cluster_id, db_name, host, port,
user_name, passwd,
b64_sql_file_contents)
finally:
return "Unhandled error"
elif arguments.runquery:
print("In run query")
db_name = arguments.get('DB_NAME')
user_name = arguments.get('USER_NAME')
passwd = arguments.get('PASSWD')
host = arguments.get('HOST')
port = arguments.get('PORT')
if port is None:
port = 5439
emp_count = arguments.get('empcount')
query_text = arguments.get('querytext')
host_list = host.split('.')
cluster_id = host_list[0]
print(query_text)
try:
if emp_count:
print("in empcount")
query_text = 'SELECT COUNT(*) FROM emp;'
result = redshift.runselectquery_text(
cluster_id, db_name, host, port, user_name, passwd,
query_text)
elif query_text:
print("in querytext")
result = redshift.runselectquery_text(
cluster_id, db_name, host, port, user_name, passwd,
query_text)
print(result)
else:
print("error in options")
finally:
return "Unhandled error"
else:
print(self.get_options())
def test_rename_cluster(self):
print("rename")
m = Provider()
print(m.rename_cluster(args={'CLUSTER_ID': 'cl12', 'newid': 'cl13'}))
def test_modify_cluster(self):
m = Provider()
print(m.modify_cluster(
args={'CLUSTER_ID': 'cl12', 'newpass': '******'}))