sql_magic - GitLite

KO | EN

gitlite — search

#javascript #python #typescript #go #rust #deep-learning #java #windows #awesome #cli #kubernetes #security #pytorch #ios

sql_magic

(★ 171)

Magic functions for using Jupyter Notebook with Apache Spark and a variety of SQL databases.

파일 탐색기

최종 버전 다운로드 (.zip)

- __init__.py
- connection.py
- exceptions.py
- notify.py
- sql_magic.py
- utils.py
- test_magic.py
- utils.py
.gitignore
LICENSE.txt
NOTICE
pytest.ini
README.md
requirements.txt
run_tests.sh
setup.cfg
setup.py
sql_magic API.ipynb

# 설치 가이드

1. 코드 내려받기

git clone https://github.com/vmware-archive/sql_magic

깃허브에서 프로젝트 코드 전체를 내 컴퓨터로 내려받습니다.

cd sql_magic

방금 내려받은 프로젝트 폴더 안으로 이동합니다.

2. Python

쉬움 추천

사전 준비물

Git GitHub에서 프로젝트 코드를 내려받으려면 필요합니다.
Python 3 설치 시 'Add Python to PATH' 옵션을 꼭 체크하세요 (Windows).

pip install -r requirements.txt

requirements.txt 등에 명시된 파이썬 라이브러리를 설치합니다.

jupyter notebook

브라우저에서 노트북(.ipynb) 파일들을 열람하고 실행할 수 있는 Jupyter 화면을 켭니다.

✅ 에러 메시지 없이 실행되고 터미널에 안내 문구가 출력되면 정상입니다.

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

sql_magic
=========

sql_magic is Jupyter magic for writing SQL to interact with Spark (or Hive) and relational databases. Query results are saved directly to a Pandas dataframe.

```sql
%%read_sql df_result
SELECT *
FROM table_name
WHERE age < {threshold}
```

<p>
  <img src="https://raw.githubusercontent.com/crawles/Logos/master/sql_magic_wide.png" width = 100%>
</p>

The sql_magic library expands upon existing libraries such as [ipython-sql](https://github.com/catherinedevlin/ipython-sql) with the following features: 
* Support for both Apache Spark and relational database connections simultaneously
* Asynchronous execution (useful for long queries)
* Browser notifications for query completion

See the included [Jupyter notebook](https://github.com/pivotal/sql_magic/blob/master/sql_magic%20API.ipynb) for examples and API usage.

## Installation

`pip install sql_magic`

## Usage: Execute SQL on a relational database

Relational databases can be accessed using [SQLAlchemy](https://www.sqlalchemy.org/) or libraries implementing the [Python DB 2.0 Specification](https://www.python.org/dev/peps/pep-0249/) (E.g., `psycopg2`, `sqlite3`, etc.).

```python
# create SQLAlchemy engine for postgres
from sqlalchemy import create_engine
postgres_engine = create_engine('postgresql://{user}:{password}@{host}:5432/{database}'.format(**connect_credentials))
```

The sql_magic library is loaded using the `%load_ext` iPython extension syntax and is pointed to the connection object as follows:

```python
%load_ext sql_magic
%config SQL.conn_name = 'postgres_engine'
```

Python variables can be directly referenced in the SQL query using the string formatting syntax:

```python
# variables for use in SQL query
table_name = 'titanic'
cols = ','.join(['age','sex','fare'])
```

SQL code is executed with the %read_sql cell magic. A browser notification containing the execution time and result dimensions will automatically appear once the query is finished.

```sql
%%read_sql df_result
SELECT {cols}
FROM {table_name}
WHERE age < 10
```

SQL syntax is colored inside Jupyter:

<img src='https://github.com/crawles/Logos/blob/master/sql_magic_syntax.png?raw=true'>

A browser notification is displayed upon query completion.

<img src='https://github.com/crawles/Logos/blob/master/notification_example.png?raw=true'>

Queries can be run again additional connection objects (Spark, Hive or relational db connections) with the `-c` or `--connection` flag:

```python
#sql_magic supports libraries following Python DB 2.0 Specification
import psycopg2
conn2 = psycopg2.connect(**connect_credentials)
```

```sql
%%read_sql df_result -c conn2
SELECT {cols}
FROM {table_name}
WHERE age < 10
```

The code can be executed asynchronously using the -a flag. Asynchronous execution is particularly useful for running long queries in the background without blocking iPython kernel.

```python
%%read_sql df_result -a
```

Since results are automatically saved as a Pandas dataframe, we can easily visualize our results using the built-in Pandas’ plotting routines:

```python
df.plot('age', 'fare', kind='scatter')
```

<img src='https://github.com/crawles/Logos/blob/master/scatter.png?raw=true'>

Multi-line SQL statements are also supported:

```sql
%%read_sql
DROP TABLE IF EXISTS table123;
CREATE TABLE table123
AS
SELECT *
FROM table456;
```

Finally, line magic synatax is also available:

```python
result = %read_sql SELECT * FROM table123;
```

## Using sql_magic with Spark or Hive

The syntax for connecting with Spark is the same as above; simply point the connection object to a SparkSession, SQLContext, or HiveContext object:

```python
# spark 2.0+
#uses SparkContext
%config SQL.conn_name = 'spark'

# spark 1.6 and before
from pyspark.sql import HiveContext  # or SQLContext
hive_context = HiveContext(sc)
%config SQL.conn_name = 'hive_context'
```

## Configuration

Both browser notifications and displaying results to standard out are enabled by default. Either of these can be temporarily disabled with the `-n` and `-d` flags, respectively. They can also be disabled using the `%config` magic function.

### Flags

Notifications and auto-display can be temporarily disabled with flags:

~~~
positional arguments:
  table_name

optional arguments:
  -h, --help     show this help message and exit
  -n, --notify   Toggle option for notifying query result
  -a, --async    Run query in seperate thread. Please be cautious when
                 assigning result to a variable
  -d, --display  Toggle option for outputing query result
~~~

### Default values

Notifications and auto-display can be disabled by default using `%config`. If this is done for either option, the flags above will temporarily enable these features.

~~~
SQL options
-------------
SQL.conn_name=<Unicode>
    Current: u'conn'
    Object name for accessing computing resource environment
SQL.notify_result=<Bool>
    Current: True
    Notify query result to stdout
SQL.output_result=<Bool>
    Current: True
    Output query result to stdout
~~~

```python
%config SQL.output_result = False  # disable browser notifications
%config SQL.notify_result = False  # disable output to std ou
```

That’s it! Give sql_magic a try and let us know what you think. Please submit a pull request for any improvements or bug fixes.

### Acknowledgements

Thank you to Scott Hajek, Greg Tam, and Srivatsan Ramanujam, along with the rest of the Pivotal Data Science team for their help in developing this library. Thank you to Lia and Jackie Ho for help with the diagram. This library was inspired from and aided by the work of the [ipython-sql](https://github.com/catherinedevlin/ipython-sql) library.