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("")