tiqds 0.1.0
Tiqets' Data Science Library
Requires Python: >=3.7.0,<3.8.0

# tiqds
Tiqets' Data Science Library

## Installation
```bash
pip install git+ssh://git@gitlab.com/tiqets/team-data/tiqds.git@master#egg=tiqds
```

Make sure you have a `.pgpass` file in your home directory:
Create a `.pgpass` file in home directory with this line of information:

`hostname:port:database:username:password.` For example: `localhost:5432:bookmarks:milosz:JOAvaDtW8SRZ2w7`

Give the file the 600 permission, otherwise psql (tiqds dependency) will ignore it, so run

```chmod 600 ~/.pgpass```

Your `.pgpass` file should have the following two lines at least:
```
tiqp-pg-cluster.cluster-ro-cxtyfd3a4vin.eu-west-1.rds.amazonaws.com:5439:tiqets_production:site_ro:<password>
tiqp-dwh-redshift.c4ktkeo4qdtz.eu-west-1.redshift.amazonaws.com:5439:tiq_dwh_production:developer_ro:<password>
```
Where `<password>` should be replaced by the password given to you.


## Local Development
Run `./venv_setup.sh` to set up a python development environment for this project. The env location will be
at `./venv/tiqds`. The `tiqds` library will be installed in _editable_ mode will all extra requirements
installed with the command `pip install -e '.[all]'`. When developing in PyCharm, point the interpreter path
to `./venv/tiqds/bin/python`.


## Tutorial


### Database basics
You can use the `tiqds.db` module to conveniently manage database
queries. Start by importing the `Query` class
```python
from tiqds.db import Query
```
Next, instantiate a query object. In this case, we will query all
columns from the `orders."order"` table from the
`redshift_prod_readonly` connection defined in
`RESOURCES/Shared/db_creds.json`. We'll also set a limit for 16 rows.
```python
query = Query(
    'SELECT * FROM orders."order" LIMIT %(limit)s;',
    connection = "redshift_prod_readonly",
    query_params={"limit": 16},
)
```

You can check out the object representation by printing it ;)
```python
print(query)
# Out: <tiqets.db.Query, parsed_sql=SELECT * FROM orders."order" LIMIT 16;>, conn=<connection object at 0x1a24200d18; dsn: 'host=tiqp-dwh-redshift.c4ktkeo4qdtz.eu-west-1.redshift.amazonaws.com port=5439 dbname=tiq_dwh_production user=developer_ro', closed: 0>
```

You can also check how the final (rendered) SQL looks like, with the
`query_params` passed:
```python
print(query.parsed_sql)
# Out: 'SELECT * FROM orders."order" LIMIT 16;'
```

Running a query is as easy as
```python
query.run()
```
Note that `.run()` will return the current Query instance, such that
`df = query.run().result`. The cleaner way of getting the result
would be to:
```python
query.run()
df = query.result
```

For queries that return a very large number of rows, you can pass the
`chunksize` argument as
```python
query = Query(
    'SELECT * FROM orders."order" LIMIT %(limit)s;',
    connection = "redshift_prod_readonly",
    query_params={"limit": 16},
    chunksize=4,
)
```
which will return only 4 rows at a time. In this case, `query.result`
is an _iterator_ (probably a _generator_) that yields
`pandas.Dataframe`s. You can use it like
```python
for df in query.result:
    print(df.head())
    # ... to something with the yielded DataFrame ...
```

I hear you ask _"What if I iterate through the `query.result` and then
want to start again without querying the DB again?"_. Well... the query
result is cached every time you call `query.run()`. You can call
`query.restore_from_cache()` which will restore `query.result` to it's
original value. Not recommended, but `query.restore_from_cache()` also
automatically returns the cached result back at you!

#### What if I just want to run a simple query?

You can also simply use the `run_query` helper function. This function takes the same keyword arguments in
the `Query` object and abstract the `.run().result` steps from you, simply returning the result. Note that 
with this abstraction you lose the `chunksize` feature. You can also use the `max_retries` argument omitted 
not used in `Query`. See the simple example bellow:
```python
df = run_query(
    sql_query='SELECT * FROM orders."order" LIMIT %(limit)s;',
    connection = "redshift_prod_readonly",
    query_params={"limit": 16},
    max_retries=2,
)
```

## TODOs
- Yuri to integrate BigQuery