def test_inverted_credentials_string(redshift):
with moto.mock_s3():
setup_table_and_bucket(redshift)
redshift.execute((
"{COMMAND} test_s3_copy_into_redshift {COLUMNS} from '{LOCATION}' "
"credentials 'aws_secret_access_key=AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;"
"aws_access_key_id=AAAAAAAAAAAAAAAAAAAA'").format(
COMMAND="COPY",
LOCATION="s3://mybucket/file.csv",
COLUMNS="",
))
# Assert order for credentials is irrelavant
fetch_values_from_table_and_assert(redshift)
def unload_fn_to_test_create_engine_patch(redshift):
with moto.mock_s3():
engine = create_engine(redshift.url)
setup_table_and_insert_data(engine)
engine.execute(
UNLOAD_TEMPLATE.format(
COMMAND="UNLOAD",
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION="AUTHORIZATION",
OPTIONAL_ARGS="",
))
fetch_values_from_s3_and_assert(engine, is_gzipped=False)
def test_random_spacing(redshift):
with moto.mock_s3():
setup_table_and_bucket(redshift)
redshift.execute(
COPY_TEMPLATE.format(
COMMAND="COPY",
LOCATION="s3://mybucket/file.csv",
COLUMNS="( i , f, c, v )",
FROM="from",
CREDENTIALS="credentials",
OPTIONAL_ARGS="",
).replace(" ", " "))
# Assert irregular spaces doesn't affect parsing.
fetch_values_from_table_and_assert(redshift)
def test_optional_keywords(redshift):
with moto.mock_s3():
setup_table_and_bucket(redshift)
redshift.execute(
COPY_TEMPLATE.format(
COMMAND="COPY",
LOCATION="s3://mybucket/file.csv",
COLUMNS="",
FROM="from",
CREDENTIALS="with credentials as",
OPTIONAL_ARGS="GZIP delimiter '|' region 'us-west-2'",
))
# Assert optional keywords are ignored.
fetch_values_from_table_and_assert(redshift)
def test_s3_copy_columns(redshift):
with moto.mock_s3():
setup_table_and_bucket(redshift)
redshift.execute(
COPY_TEMPLATE.format(
COMMAND="copy",
LOCATION="s3://mybucket/file.csv",
COLUMNS="(i, f, c, v)",
FROM="from",
CREDENTIALS="credentials",
OPTIONAL_ARGS="",
))
# Assert support for Columns
fetch_values_from_table_and_assert(redshift)
def test_unload_case_senesitivity(redshift):
"""Test case sensitivity for UNLOAD command."""
with moto.mock_s3():
setup_table_and_insert_data(redshift)
redshift.execute(
UNLOAD_TEMPLATE.format(
COMMAND=randomcase("UNLOAD"),
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO=randomcase("TO"),
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION=randomcase("AUTHORIZATION"),
OPTIONAL_ARGS="",
))
fetch_values_from_s3_and_assert(redshift, is_gzipped=False)
def test_unload(redshift):
"""Test if a file is created with the appropriate data."""
with moto.mock_s3():
setup_table_and_insert_data(redshift)
redshift.execute(
UNLOAD_TEMPLATE.format(
COMMAND="UNLOAD",
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION="AUTHORIZATION",
OPTIONAL_ARGS="",
))
fetch_values_from_s3_and_assert(redshift, is_gzipped=False)
def copy_fn_to_test_create_engine_patch(redshift):
with moto.mock_s3():
engine = create_engine(redshift.url)
setup_table_and_bucket(engine)
engine.execute(
COPY_TEMPLATE.format(
COMMAND="COPY",
LOCATION="s3://mybucket/file.csv",
COLUMNS="",
FROM="from",
CREDENTIALS="credentials",
OPTIONAL_ARGS="",
))
fetch_values_from_table_and_assert(engine)
def unload_fn_to_test_psycopg2_connect_patch(config):
with moto.mock_s3():
conn = psycopg2.connect(**config)
cursor = conn.cursor()
setup_table_and_insert_data(cursor)
cursor.execute(
UNLOAD_TEMPLATE.format(
COMMAND="UNLOAD",
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION="AUTHORIZATION",
OPTIONAL_ARGS="",
))
fetch_values_from_s3_and_assert(cursor, is_gzipped=False)
def copy_fn_to_test_psycopg2_connect_patch_as_context_manager(config):
with moto.mock_s3():
with psycopg2.connect(**config) as conn:
with conn.cursor() as cursor:
setup_table_and_bucket(cursor)
cursor.execute(
COPY_TEMPLATE.format(
COMMAND="COPY",
LOCATION="s3://mybucket/file.csv",
COLUMNS="",
FROM="from",
CREDENTIALS="credentials",
OPTIONAL_ARGS="",
))
fetch_and_assert_psycopg2(cursor)
def test_optional_keywords(redshift):
"""Test command with optimal keyword arguments."""
with moto.mock_s3():
setup_table_and_insert_data(redshift)
redshift.execute(
UNLOAD_TEMPLATE.format(
COMMAND="UNLOAD",
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION="WITH AUTHORIZATION AS",
OPTIONAL_ARGS="DELIMITER AS ','",
)
)
fetch_values_from_s3_and_assert(redshift, is_gzipped=False, delimiter=",")
def test_unload_gzipped(redshift):
"""Test gzip support."""
with moto.mock_s3():
setup_table_and_insert_data(redshift)
redshift.execute(
UNLOAD_TEMPLATE.format(
COMMAND="UNLOAD",
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv.gz",
AUTHORIZATION="AUTHORIZATION",
OPTIONAL_ARGS="GZIP",
)
)
fetch_values_from_s3_and_assert(redshift, file_name="myfile.csv.gz", is_gzipped=True)
def copy_fn_to_test_psycopg2_connect_patch(config):
with moto.mock_s3():
conn = psycopg2.connect(**config)
cursor = conn.cursor()
setup_table_and_bucket(cursor)
cursor.execute(
COPY_TEMPLATE.format(
COMMAND="COPY",
LOCATION="s3://mybucket/file.csv",
COLUMNS="(i, f, c, v)",
FROM="from",
CREDENTIALS="credentials",
OPTIONAL_ARGS="EMPTYASNULL",
)
)
fetch_and_assert_psycopg2(cursor)
def test_inverted_credentials_string(redshift):
"""Test parsing with an inverted credentials string."""
with moto.mock_s3():
setup_table_and_insert_data(redshift)
redshift.execute((
"{COMMAND} ({SELECT_STATEMENT}) {TO} '{LOCATION}' "
"{AUTHORIZATION} 'aws_secret_access_key=AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;"
"aws_access_key_id=AAAAAAAAAAAAAAAAAAAA'"
"{OPTIONAL_ARGS};").format(
COMMAND="UNLOAD",
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION="AUTHORIZATION",
OPTIONAL_ARGS="",
))
fetch_values_from_s3_and_assert(redshift, is_gzipped=False)
def test_multiple_sql_statemts(redshift):
with moto.mock_s3():
redshift.execute(
("CREATE TEMP TABLE test_s3_unload_from_redshift "
"(i INT, f FLOAT, c CHAR(1), v VARCHAR(16));"
"INSERT INTO test_s3_unload_from_redshift(i, f, c, v)"
" values(3342, 32434.0, 'a', 'gfhsdgaf'), (3343, 0, 'b', NULL), "
"(0, 32434.0, NULL, 'gfhsdgaf');"
"{COMMAND} ({SELECT_STATEMENT}) {TO} '{LOCATION}' "
"{AUTHORIZATION} 'aws_access_key_id=AAAAAAAAAAAAAAAAAAAA;"
"aws_secret_access_key=AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' "
"{OPTIONAL_ARGS}".format(
COMMAND="UNLOAD",
SELECT_STATEMENT="select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION="AUTHORIZATION",
OPTIONAL_ARGS="",
)))
fetch_values_from_s3_and_assert(redshift, is_gzipped=False)
def test_ignores_sqlalchmey_text_obj(redshift):
"""Test command ignores SQLAlchemy Text Objects and raises error."""
with moto.mock_s3():
setup_table_and_insert_data(redshift)
try:
redshift.execute(
text(
UNLOAD_TEMPLATE.format(
COMMAND=" UNLOAD",
SELECT_STATEMENT=
"select * from test_s3_unload_from_redshift",
TO="TO",
LOCATION="s3://mybucket/myfile.csv",
AUTHORIZATION="AUTHORIZATION",
OPTIONAL_ARGS="DELIMITER AS ','",
)))
# The default engine will try to execute an `UNLOAD` command and will fail, raising a
# ProgrammingError
except sqlalchemy.exc.ProgrammingError:
return
