def _create_table(
df: pd.DataFrame,
cursor: "pyodbc.Cursor",
table: str,
schema: str,
mode: str,
index: bool,
dtype: Optional[Dict[str, str]],
varchar_lengths: Optional[Dict[str, int]],
) -> None:
if mode == "overwrite":
_drop_table(cursor=cursor, schema=schema, table=table)
elif _does_table_exist(cursor=cursor, schema=schema, table=table):
return
sqlserver_types: Dict[str, str] = _data_types.database_types_from_pandas(
df=df,
index=index,
dtype=dtype,
varchar_lengths_default="VARCHAR(MAX)",
varchar_lengths=varchar_lengths,
converter_func=_data_types.pyarrow2sqlserver,
)
cols_str: str = "".join([f"{k} {v},\n" for k, v in sqlserver_types.items()])[:-2]
table_identifier = _get_table_identifier(schema, table)
sql = (
f"IF OBJECT_ID(N'{table_identifier}', N'U') IS NULL BEGIN CREATE TABLE {table_identifier} (\n{cols_str}); END;"
)
_logger.debug("Create table query:\n%s", sql)
cursor.execute(sql)
def _create_table(
df: pd.DataFrame,
cursor: pg8000.Cursor,
table: str,
schema: str,
mode: str,
index: bool,
dtype: Optional[Dict[str, str]],
varchar_lengths: Optional[Dict[str, int]],
) -> None:
if mode == "overwrite":
_drop_table(cursor=cursor, schema=schema, table=table)
elif _does_table_exist(cursor=cursor, schema=schema, table=table):
return
postgresql_types: Dict[str, str] = _data_types.database_types_from_pandas(
df=df,
index=index,
dtype=dtype,
varchar_lengths_default="TEXT",
varchar_lengths=varchar_lengths,
converter_func=_data_types.pyarrow2postgresql,
)
cols_str: str = "".join(
[f"{k} {v},\n" for k, v in postgresql_types.items()])[:-2]
sql = f'CREATE TABLE IF NOT EXISTS "{schema}"."{table}" (\n{cols_str})'
_logger.debug("Create table query:\n%s", sql)
cursor.execute(sql)
def _create_table(
df: Optional[pd.DataFrame],
path: Optional[Union[str, List[str]]],
cursor: redshift_connector.Cursor,
table: str,
schema: str,
mode: str,
index: bool,
dtype: Optional[Dict[str, str]],
diststyle: str,
sortstyle: str,
distkey: Optional[str],
sortkey: Optional[List[str]],
primary_keys: Optional[List[str]],
varchar_lengths_default: int,
varchar_lengths: Optional[Dict[str, int]],
parquet_infer_sampling: float = 1.0,
use_threads: bool = True,
boto3_session: Optional[boto3.Session] = None,
s3_additional_kwargs: Optional[Dict[str, str]] = None,
) -> Tuple[str, Optional[str]]:
if mode == "overwrite":
_drop_table(cursor=cursor, schema=schema, table=table)
elif _does_table_exist(cursor=cursor, schema=schema, table=table) is True:
if mode == "upsert":
guid: str = uuid.uuid4().hex
temp_table: str = f"temp_redshift_{guid}"
sql: str = f"CREATE TEMPORARY TABLE {temp_table} (LIKE {schema}.{table})"
_logger.debug(sql)
cursor.execute(sql)
return temp_table, None
return table, schema
diststyle = diststyle.upper() if diststyle else "AUTO"
sortstyle = sortstyle.upper() if sortstyle else "COMPOUND"
if df is not None:
redshift_types: Dict[
str, str] = _data_types.database_types_from_pandas(
df=df,
index=index,
dtype=dtype,
varchar_lengths_default=varchar_lengths_default,
varchar_lengths=varchar_lengths,
converter_func=_data_types.pyarrow2redshift,
)
elif path is not None:
redshift_types = _redshift_types_from_path(
path=path,
varchar_lengths_default=varchar_lengths_default,
varchar_lengths=varchar_lengths,
parquet_infer_sampling=parquet_infer_sampling,
use_threads=use_threads,
boto3_session=boto3_session,
s3_additional_kwargs=s3_additional_kwargs,
)
else:
raise ValueError("df and path are None.You MUST pass at least one.")
_validate_parameters(
redshift_types=redshift_types,
diststyle=diststyle,
distkey=distkey,
sortstyle=sortstyle,
sortkey=sortkey,
)
cols_str: str = "".join([f"{k} {v},\n"
for k, v in redshift_types.items()])[:-2]
primary_keys_str: str = f",\nPRIMARY KEY ({', '.join(primary_keys)})" if primary_keys else ""
distkey_str: str = f"\nDISTKEY({distkey})" if distkey and diststyle == "KEY" else ""
sortkey_str: str = f"\n{sortstyle} SORTKEY({','.join(sortkey)})" if sortkey else ""
sql = (f"CREATE TABLE IF NOT EXISTS {schema}.{table} (\n"
f"{cols_str}"
f"{primary_keys_str}"
f")\nDISTSTYLE {diststyle}"
f"{distkey_str}"
f"{sortkey_str}")
_logger.debug("Create table query:\n%s", sql)
cursor.execute(sql)
return table, schema
