def test_pandas_read_basic(ingestion_test_repo):
df_to_table(base_df, ingestion_test_repo, "test_table", if_exists="patch")
old = ingestion_test_repo.commit()
# We currently don't detect the index column name since it's an arbitrary query that's passed.
output = sql_to_df("SELECT * FROM test_table",
repository=ingestion_test_repo,
index_col="fruit_id")
assert_frame_equal(base_df, output)
def test_pandas_read_other_checkout(ingestion_test_repo):
df_to_table(base_df, ingestion_test_repo, "test_table", if_exists="patch")
old = ingestion_test_repo.commit()
df_to_table(upd_df_1, ingestion_test_repo, "test_table", if_exists="patch")
new = ingestion_test_repo.commit()
# Record the second version of the table
patched_df = sql_to_df("SELECT * FROM test_table", repository=ingestion_test_repo)
# Check out the old version but run the query against the new image -- new version should come out.
old.checkout()
output_1 = sql_to_df("SELECT * FROM test_table", image=new)
assert_frame_equal(output_1, patched_df)
# Test works with hashes
output_2 = sql_to_df(
"SELECT * FROM test_table", image=new.image_hash[:10], repository=ingestion_test_repo
)
assert_frame_equal(output_2, patched_df)
def test_pandas_kv(ingestion_test_repo):
# Test reads and writes with a key-value type dataframe.
df = pd.read_csv(os.path.join(INGESTION_RESOURCES_CSV, "base_df_kv.csv"),
index_col=0)
df_to_table(df, ingestion_test_repo, "test_table")
# Test patching works without specifying an index col
df = pd.read_csv(os.path.join(INGESTION_RESOURCES_CSV, "patch_df_kv.csv"))
df_to_table(df, ingestion_test_repo, "test_table", if_exists="patch")
assert_frame_equal(
sql_to_df("SELECT * FROM test_table", repository=ingestion_test_repo),
pd.DataFrame(data=[(1, "banana"), (2, "kumquat"), (3, "pendulum")],
columns=["key", "value"]),
)
def test_pandas_read_lq_checkout(ingestion_test_repo):
df_to_table(base_df, ingestion_test_repo, "test_table", if_exists="patch")
old = ingestion_test_repo.commit()
# Record a DF with just the values for fruit_id = 3 and 4 (ones that will be updated).
query = "SELECT * FROM test_table WHERE fruit_id IN (3, 4)"
old_3_4 = sql_to_df(query, repository=ingestion_test_repo)
df_to_table(upd_df_1, ingestion_test_repo, "test_table", if_exists="patch")
new = ingestion_test_repo.commit()
new_3_4 = sql_to_df(query, repository=ingestion_test_repo)
# Uncheckout the repo but don't delete it.
ingestion_test_repo.uncheckout()
assert ingestion_test_repo.head is None
output_old = sql_to_df(query, image=old, use_lq=True)
assert_frame_equal(old_3_4, output_old)
output_new = sql_to_df(query, image=new, use_lq=True)
assert_frame_equal(new_3_4, output_new)
# Make sure we didn't do an actual checkout.
assert ingestion_test_repo.head is None
def test_pandas_read_roundtripping(ingestion_test_repo):
df_to_table(base_df, ingestion_test_repo, "test_table", if_exists="patch")
old = ingestion_test_repo.commit()
df_to_table(upd_df_1, ingestion_test_repo, "test_table", if_exists="patch")
new = ingestion_test_repo.commit()
df = sql_to_df("SELECT * FROM test_table", repository=ingestion_test_repo, index_col="fruit_id")
# Pandas update syntax: update index 4 (fruit ID) to have a new timestamp.
df.at[4, "timestamp"] = dt(2018, 1, 1, 1, 1, 1)
# Write the whole df back in patch mode -- despite that, the changeset will only contain the updated cell.
df_to_table(df, ingestion_test_repo, "test_table", if_exists="patch")
new_2 = ingestion_test_repo.commit()
assert ingestion_test_repo.diff("test_table", new, new_2) == [
(False, (4, dt(2018, 12, 30, 0, 0), "chandelier")),
(True, (4, dt(2018, 1, 1, 1, 1, 1), "chandelier")),
]
def run(self, repo_uri: str = None, query: str = None, schema: Schema = None, layer_query: bool = None, **kwargs: Any):
"""
Args:
Returns:
- No return
"""
assert repo_uri, 'Must specify repo_uri.'
repo_info = parse_repo(repo_uri)
repo = Repository(namespace=repo_info.namespace, repository=repo_info.repository)
data = sql_to_df(query, repository=repo, use_lq=layer_query)
if schema is not None:
errors = schema.validate(data)
if errors:
raise SchemaValidationError(errors)
return data
def read(self, location: str) -> Result:
new = self.copy()
new.location = location
try:
repo = Repository(namespace=new.repo_info.namespace,
repository=new.repo_info.repository)
remote = Repository.from_template(repo,
engine=get_engine(
new.repo_info.remote_name,
autocommit=True))
cloned_repo = clone(
remote,
local_repository=repo,
download_all=True,
overwrite_objects=True,
overwrite_tags=True,
single_image=new.repo_info.tag,
)
data = sql_to_df(f"SELECT * FROM {new.repo_info.table}",
repository=cloned_repo,
use_lq=self.layer_query)
if self.schema is not None:
errors = self.schema.validate(data)
if errors:
raise SchemaValidationError(errors)
new.value = data
except Exception as exc:
self.logger.exception(
"Unexpected error while reading from result handler: {}".
format(repr(exc)))
raise exc
return new
import scipy.stats as ss
from splitgraph.core.repository import Repository
from splitgraph.ingestion.pandas import sql_to_df
# Load the dataset we created into Pandas
image = Repository("", "qoz_vote_fraction").images["latest"]
df = sql_to_df("SELECT * FROM qoz_vote_fraction", image=image, use_lq=True)
print(df)
# Is there a correlation between the Trump vote fraction and the fraction of
# QOZ-qualified tracts in every county?
print(ss.linregress(df["trump_vote_fraction"], df["qoz_tract_fraction"]))