def grant(self, privilege, level, vschema, vtable=None, to=None):
"""
Grants 'privilege' on 'vschema'.'vtable' to 'entity'. An 'entity' can be a
user or a group.
@type privilege: str
@param privilege: 'USAGE', 'SELECT', 'CREATE', 'DELETE' etc
@type vschema: str
@param vschema: Vertica schema
@type vtable: str
@param vtable: Vertica table
@type to: str
@param to: user or a group, separated by commas
@rtype: None
@return: None
"""
# If table is not specified, the privileges are to be granted at the Schema level
if (level.lower() == "schema") and (privilege.upper() == "SELECT"):
grant_stmt = "grant SELECT on all tables in schema %s to %s" % (
vschema, to)
elif (level.lower() == "schema") and (privilege.upper() == "USAGE"):
grant_stmt = "grant USAGE on schema %s to %s" % (vschema, to)
elif (level.lower() == "table") and (privilege.upper() == "SELECT"):
grant_stmt = "grant SELECT on table %s.%s to %s" % (vschema,
vtable, to)
else:
logkv(
logger, {
"msg": "Incorrect level/privilege combination",
"level": level,
"privilege": privilege
}, "error")
raise VerticaManagerException()
try:
self.execute(stmt=grant_stmt)
logkv(
logger, {
"msg": "Granted privileges",
"granted_to": to,
"privilege": privilege,
"vschema": vschema,
"vtable": vtable
}, "info")
except VerticaManagerException as ex:
logkv(logger, {
"msg": "Error executing grant statement",
"stmt": grant_stmt
}, "error", ex)
raise
def execute(self, stmt=None, scriptfile=None):
"""
Execute sql stmt 'stmt' or sql script file 'scriptfile'
@type stmt: str
@param stmt: SQL query string
@type scriptfile: str
@param scriptfile: Location of scriptfile containing commands to execute
@rtype: str
@return: Output of the shell command enclosing Vertica sql command
"""
# Get the execution mode and the argument (statement or filename)
if stmt and not scriptfile:
# The stmt *needs* to be in double quotes since it could contain
# single-quoted strings (see load method below)
vsql_cmd = '%s -c "%s" ' % (self.vsql, stmt)
elif scriptfile and not stmt:
vsql_cmd = "%s -f '%s'" % (self.vsql, scriptfile)
else:
logkv(
logger, {
"msg": "Received incorrect or conflicting execution mode",
"stmt": stmt,
"scriptfile": scriptfile
}, "info")
raise VerticaManagerException("Incorrect execution mode")
try:
vresult = self.shell_exec.safe_execute(vsql_cmd,
verbose=False,
as_shell=True,
splitcmd=False)
return vresult
except ShellException:
logkv(logger, {
"msg": "VSQL command failed",
"cmd": vsql_cmd
}, "error")
raise VerticaManagerException()
def rollback(self,
srcschema,
srctable,
rollbackschema,
rollbacktable,
rkey=None):
"""
Deletes rows in srctable that are present in rollbacktable.
@type srcschema: str
@param srcschema: Schema of the souce table
@type srctable: str
@param srctable: Source table
@type rollbackschema: str
@param rollbackschema: Schema of the table containing rollback data
@type rollbacktable: str
@param rollbacktable: Table containing the rollback data
@rtype: str
@return: Count of rows deleted
"""
try:
rbk_stmt = '''
set session autocommit to on;
delete from %s.%s
where %s in (
select %s
from %s.%s
);''' % (srcschema, srctable, rkey, rkey,
rollbackschema, rollbacktable)
vresult = self.execute(stmt=rbk_stmt)
rows = VerticaManager.getrows(vresult.output)
logkv(
logger, {
"msg": "rollback successful",
"source_schema": srcschema,
"source_table": srctable,
"rows": rows
}, "info")
return rows
except VerticaManagerException as ex:
logkv(logger, {"msg": "VSQL table drop failed"}, "error", ex)
raise VerticaManagerException()
def getrows(vcopy_output):
"""
Parses the console output of COPY command and extracts number of rows loaded
@type vcopy_output: str
@param vcopy_output: Output of COPY command
@rtype: int
@return: Number of rows loaded
"""
pattern = "\s*(Rows Loaded|count|OUTPUT)\s*.*\s*([0-9]*)\s*"
try:
return re.findall(pattern, vcopy_output)[0][1]
except Exception:
logkv(
logger, {
"msg":
"Error retriving rows loaded from output of COPY command",
"voutput": vcopy_output,
"pattern": pattern
}, "error")
raise VerticaManagerException()
def test_truncate_exception(self, mock_vexec):
mock_vexec.side_effect = VerticaManagerException()
with self.assertRaises(VerticaManagerException):
self.tvm.truncate("foo", "bar")
def load(self,
webhdfs_root,
hdfs_path,
vschema,
dtable,
rtable,
mode="direct"):
"""
Loads data from HDFS into Vertica table 'dtable
@type webhdfs_root: str
@param hdfs_path: WebHdfs prefix. Same as hadoop name node
@type hdfs_path: str
@param hdfs_path: HDFS path to dataset
@type vschema: str
@param vschema: Vertica schema
@type dtable: str
@param dtable: Vertica table for data
@type rtable: str
@param rtable: Vertica table for rejected rows
@type mode: str
@param mode: Copy mode. Possible values: 'direct' or 'decompress'. If mode is
'decompress', the data in Hive partition will be decompressed to a plain text
format using a single node to a temporary HDFS location. IF the mode='direct'
an attempt will be made to directly load compressed data to vertica using the
appropriate filter.
mode='decompress' is required if a the MapReduce job outputs data in a compression
format not supported by Vertica filter function. For example BZip2. In this
case, we'll decompress the data before passing it to the COPY command
@rtype: str
@return: Number of rows loaded
"""
if mode == "direct":
_filter = "FILTER GZIP()"
elif mode == "decompress":
_filter = ""
else:
logkv(
logger, {
"msg":
"Invalid load mode supplied to Vertica COPY command",
"mode": mode
}, "error")
raise VerticaManagerException()
# Discard the leading "/" in HDFS path. We're going to be pre-pending it with
# webhdfs base URL
if hdfs_path.startswith("/"):
hdfs_path = hdfs_path[1:]
webhdfs_url = os.path.join(webhdfs_root, hdfs_path)
copy_cmd = '''COPY %s.%s
SOURCE Hdfs(url=\'%s\', username=\'%s\', low_speed_limit=1048576)
%s
DELIMITER E\'\\001\'
REJECTMAX 0
REJECTED DATA AS TABLE %s.%s
DIRECT
COMMIT
''' % (vschema, dtable, webhdfs_url,
self.connection_info["vertica_user"], _filter,
vschema, rtable)
try:
vresult = self.execute(stmt=copy_cmd)
rows_loaded = VerticaManager.getrows(vresult.output)
logkv(
logger, {
"msg": "Loaded data in HDFS path to Vertica table",
"hdfs_path": hdfs_path,
"vschema": vschema,
"dtable": dtable,
"rows_loaded": rows_loaded
}, "info")
return rows_loaded
except VerticaManagerException as ex:
logkv(logger, {"msg": "Load to Vertica via WebHdfs failed"},
"error", ex)
raise
def test_rollback_exception(self, mock_vexec):
mock_vexec.side_effect = VerticaManagerException()
with self.assertRaises(VerticaManagerException):
_ = self.tvm.rollback("schema_foo", "table_foo", "schema_bar",
"table_bar", "keyfoo")
def test_grant_exception(self, mock_vexec):
mock_vexec.side_effect = VerticaManagerException()
privilege, level, vschema, to = "SELECT", "schema", "foo", "userfoo"
with self.assertRaises(VerticaManagerException):
self.tvm.grant(privilege, level, vschema, None, to)
def test_load_exception(self, mock_vexec):
mock_vexec.side_effect = VerticaManagerException()
with self.assertRaises(VerticaManagerException):
_ = self.tvm.load("foo", "bar", "foo", "bar", "foo")
def test_clone_schema_exception(self, mock_vexec):
mock_vexec.side_effect = VerticaManagerException()
with self.assertRaises(VerticaManagerException):
self.tvm.clone_schema("foo", "bar", "foo", "bar")
def test_create_table_shell_exception(self, mock_vexec):
mock_vexec.side_effect = VerticaManagerException()
with self.assertRaises(VerticaManagerException):
self.tvm.create_table("")