Greenplum Python工具库gpload学习——get_fast_match_exttable_query和get_reuse_exttable_query（greenplumn）

Greenplum Python工具库gpload学习——get_fast_match_exttable_query和get_reuse_exttable_query（greenplumn）

查询可重复利用的外部表，只指定location、data format和error limit相同

# Fast path to find out whether we have an existing external table in the # catalog which could be reused for this operation. we only make sure the # location, data format and error limit are same. we don't check column # names and types # # This function will return the SQL to run in order to find out whether # such a table exists. The results of this SQl are table names without schema # def get_fast_match_exttable_query(self, formatType, formatOpts, limitStr, schemaName, log_errors, encodingCode): sqlFormat = """select relname from pg_class join pg_exttable pgext on(pg_class.oid = pgext.reloid) %s where relstorage = 'x' and relname like 'ext_gpload_reusable_%%' and %s """ joinStr = "" conditionStr = "" # if schemaName is None, find the resuable ext table which is visible to # current search path. Else find the resuable ext table under the specific # schema, and this needs to join pg_namespace. if schemaName is None: joinStr = "" conditionStr = "pg_table_is_visible(pg_class.oid)" else: joinStr = """join pg_namespace pgns on(pg_class.relnamespace = pgns.oid)""" conditionStr = "pgns.nspname = '%s'" % schemaName sql = sqlFormat % (joinStr, conditionStr) if self.gpdb_version < "6.0.0": if log_errors: sql += " and pgext.fmterrtbl = pgext.reloid " else: sql += " and pgext.fmterrtbl IS NULL " else: if log_errors: sql += " and pgext.logerrors " else: sql += " and NOT pgext.logerrors " for i, l in enumerate(self.locations): sql += " and pgext.urilocation[%s] = %s\n" % (i + 1, quote(l)) sql+= """and pgext.fmttype = %s and pgext.writable = false and pgext.fmtopts like %s """ % (quote(formatType[0]),quote("%" + quote_unident(formatOpts.rstrip()) +"%")) if limitStr: sql += "and pgext.rejectlimit = %s " % limitStr else: sql += "and pgext.rejectlimit IS NULL " if encodingCode: sql += "and pgext.encoding = %s " % encodingCode sql+= "limit 1;" self.log(self.DEBUG, "query used to fast match external relations:\n %s" % sql) return

查询可重复利用的外部表，指定column names and types, the same data format, and location specification, and single row error handling specs相同

def get_reuse_exttable_query(self, formatType, formatOpts, limitStr, from_cols, schemaName, log_errors, encodingCode): sqlFormat = """select attrelid::regclass from ( select attrelid, row_number() over (partition by attrelid order by attnum) as attord, attnum, attname, atttypid::regtype from pg_attribute join pg_class on (pg_class.oid = attrelid) %s where relstorage = 'x' and relname like 'ext_gpload_reusable_%%' and attnum > 0 and not attisdropped and %s ) pgattr join pg_exttable pgext on(pgattr.attrelid = pgext.reloid) """ joinStr = "" conditionStr = "" # if schemaName is None, find the resuable ext table which is visible to # current search path. Else find the resuable ext table under the specific # schema, and this needs to join pg_namespace. if schemaName is None: joinStr = "" conditionStr = "pg_table_is_visible(pg_class.oid)" else: joinStr = """join pg_namespace pgns on(pg_class.relnamespace = pgns.oid) """ conditionStr = "pgns.nspname = '%s'" % schemaName sql = sqlFormat % (joinStr, conditionStr) if self.gpdb_version < "6.0.0": if log_errors: sql += " WHERE pgext.fmterrtbl = pgext.reloid " else: sql += " WHERE pgext.fmterrtbl IS NULL " else: if log_errors: sql += " WHERE pgext.logerrors " else: sql += " WHERE NOT pgext.logerrors " for i, l in enumerate(self.locations): sql += " and pgext.urilocation[%s] = %s\n" % (i + 1, quote(l)) sql+= """and pgext.fmttype = %s and pgext.writable = false and pgext.fmtopts like %s """ % (quote(formatType[0]),quote("%" + quote_unident(formatOpts.rstrip()) +"%")) if limitStr: sql += "and pgext.rejectlimit = %s " % limitStr else: sql += "and pgext.rejectlimit IS NULL " if encodingCode: sql += "and pgext.encoding = %s " % encodingCode sql+= "group by attrelid " sql+= """having count(*) = %s and bool_and(case """ % len(from_cols) for i, c in enumerate(from_cols): name = c[0] typ = c[1] sql+= "when attord = %s then atttypid = %s::regtype and attname = %s\n" % (i+1, quote(typ), quote(quote_unident(name))) sql+= """else true end) limit 1;""" self.log(self.DEBUG, "query used to identify reusable external relations: %s" % sql) return

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。