{
"cells": [
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"## Datatype Support\n",
"`redshift_connector` supports Amazon Redshift specific datatypes in order to provide users integration of their data into Python projects. Please see the projects [README](https://github.com/aws/amazon-redshift-python-driver/blob/master/README.rst) for a list of supported datatypes."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Examples\n",
"The following sections provide basic examples showing how to work with Amazon Redshift datatypes.\n",
"\n",
"#### Geometry\n",
"- **Send**: A string holding geometry data in WKB (well known binary) format.\n",
"- **Receive**: A string holding geometry data in WKB format.\n",
"\n",
"**Note**: Geometry data can be sent and receive in formats other than WKB if Amazon Redshift spatial functions are applied. Please see the [Amazon Redshift documentation for a list of spacial functions](https://docs.aws.amazon.com/redshift/latest/dg/geospatial-functions.html).\n",
"\n",
"[Geometry](https://docs.aws.amazon.com/redshift/latest/dg/GeometryType-function.html)\n",
"\n",
"Sending data in WKB format:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import redshift_connector\n",
"\n",
"with redshift_connector.connect(...) as conn:\n",
" with conn.cursor() as cursor:\n",
" cursor.execute(\"create table datatype_test (c1 geometry);\")\n",
" cursor.execute(\n",
" \"insert into datatype_test (c1) values (%s);\",\n",
" (\n",
" '0103000020E61000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000',\n",
" # using WKB format\n",
" )\n",
" )\n",
" cursor.execute(\"select c1 from datatype_test;\")\n",
" result = cursor.fetchone()\n",
" print(\"c1={}\\n\".format(result[0],))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Sending data in WKT (well known text) format:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import redshift_connector\n",
"\n",
"with redshift_connector.connect(...) as conn:\n",
" with conn.cursor() as cursor:\n",
" cursor.execute(\"create table datatype_test (c1 geometry);\")\n",
" cursor.execute(\n",
" \"insert into datatype_test (c1) values (ST_GeomFromText(%s));\",\n",
" (\n",
" 'LINESTRING(1 2,3 4,5 6,7 8,9 10,11 12,13 14,15 16,17 18,19 20)', # using WKT format\n",
" )\n",
" )\n",
" cursor.execute(\"select c1, c2 from datatype_test;\")\n",
" result = cursor.fetchone()\n",
" print(\"c1={}\\nc2={}\".format(result[0], result[1]))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Super\n",
"- **Send**: A string containing JSON data.\n",
"- **Receive**: A string containing JSON data\n",
"\n",
"[Super](https://docs.aws.amazon.com/redshift/latest/dg/r_SUPER_type.html)\n",
"[json_parse](https://docs.aws.amazon.com/redshift/latest/dg/JSON_PARSE.html)\n",
"[Unnesting SUPER arrays](https://docs.aws.amazon.com/redshift/latest/dg/query-super.html#unnest)\n",
"[Querying semistructured data](https://docs.aws.amazon.com/redshift/latest/dg/query-super.html)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import redshift_connector\n",
"\n",
"with redshift_connector.connect(...) as conn:\n",
" with conn.cursor() as cursor:\n",
" cursor.execute(\n",
" \"CREATE TABLE foo AS SELECT json_parse(%s) AS multi_level_array;\",\n",
" ('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]',)\n",
" )\n",
" cursor.execute(\"SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;\")\n",
" result = cursor.fetchall()\n",
" print(result)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Retrieving array elements from json array stored in super datatype"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import redshift_connector \n",
"import json\n",
"\n",
"with redshift_connector.connect(...) as conn:\n",
" with conn.cursor() as cursor:\n",
" cursor.execute(\n",
" \"CREATE TABLE foo AS SELECT json_parse(%s) AS vals;\",\n",
" (json.dumps({\"x\": [1,2,3,4], \"y\": [5,6,7,8], \"z\": [9,10,11,12]}),)\n",
" )\n",
" cursor.execute(\"SELECT vals.x FROM foo;\")\n",
" result = cursor.fetchall()\n",
" print(result)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import redshift_connector \n",
"import json\n",
"\n",
"with redshift_connector.connect(...) as conn:\n",
" with conn.cursor() as cursor:\n",
" cursor.execute(\"create table t (s super);\")\n",
" cursor.execute(\"insert into t values (json_parse(%s));\", ('[10001,10002,\"abc\"]',))\n",
" cursor.execute(\"insert into t values (json_parse(%s));\", (json.dumps({\"x\": [1,2,3,4]}),))\n",
" cursor.execute(\"select s from t;\")\n",
" result = cursor.fetchall()\n",
" print(result)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Varbyte\n",
"- **Send**: A string or bytes\n",
"- **Receive**: A string containing data in hexidecimal format\n",
"\n",
"[Varbyte](https://docs.aws.amazon.com/redshift/latest/dg/r_VARBYTE_type.html)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import redshift_connector\n",
"\n",
"with redshift_connector.connect(...) as conn:\n",
" with conn.cursor() as cursor:\n",
" cursor.execute(\"create table t (v varbyte);\")\n",
" cursor.execute(\"insert into t values (%s), (%s);\", ('aa', 'abc', ))\n",
" cursor.execute(\"insert into t values (%s), (%s);\", (b'aa', b'abc',))\n",
" cursor.execute(\"insert into t values (%s), (%s);\", (b'\\x00\\x01\\x02\\x03',b'\\x00\\x0a\\x0b\\x0c'))\n",
" cursor.execute(\"select v from t;\")\n",
" result = cursor.fetchall()\n",
" print(result)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.7"
}
},
"nbformat": 4,
"nbformat_minor": 1
}