{ "info": { "author": "", "author_email": "creisle@bcgsc.ca", "bugtrack_url": null, "classifiers": [ "License :: OSI Approved :: MIT License" ], "description": "\nSqlalchemy Hawq\n===============\n\n\n.. image:: https://travis-ci.org/bcgsc/sqlalchemy_hawq.svg?branch=master\n :target: https://travis-ci.org/bcgsc/sqlalchemy_hawq\n :alt: Build Status\n\n\n\n* `Getting Started <#getting-started>`_\n\n * `Install (For developers) <#install-for-developers>`_\n * `Run Tests <#run-tests>`_\n * `Deploy (For developers) <#deploy-for-developers>`_\n\n* `Using in a SQLAlchemy project <#using-in-a-sqlalchemy-project>`_\n\n * `How to incorporate sqlalchemy-hawq <#how-to-incorporate-sqlalchemy-hawq>`_\n * `Hawq-specific table arguments <#hawq-specific-table-arguments>`_\n * `Example of hawq table arguments with declarative syntax <#example-of-hawq-table-arguments-with-declarative-syntax>`_\n\n* `Using partitions <#using-partitions>`_\n\nThis is a custom dialect for using SQLAlchemy with a `HAWQ `_\ndatabase.\n\nIt extends the Postgresql dialect.\n\nFeatures include:\n\n\n* Hawq options for 'CREATE TABLE' statements\n* a point class\n* a modified 'DELETE' statement for compatibility with SQLAlchemy's test suite\n\nUnless specifically overridden, any functionality in SQLAlchemy's Postgresql dialect is also available. Note that in general, functionality that is available in Postgresql but not in Hawq has not yet been disabled.\n\nGetting Started\n---------------\n\nInstall (For developers)\n^^^^^^^^^^^^^^^^^^^^^^^^\n\nclone this repository\n\n.. code::\n\n git clone https://creisle@svn.bcgsc.ca/bitbucket/scm/dat/sqlalchemy_hawq.git\n cd sqlalchemy_hawq\n\ncreate a virtual environment\n\n.. code::\n\n python3 -m venv venv\n source venv/bin/activate\n\ninstall the package and its development dependencies\n\n.. code::\n\n pip install -e .[dev]\n\nRun Tests\n^^^^^^^^^\n\nsqlalchemy_hawq incorporates the standard SQLAlchemy test suite as well as some tests of its own. Run them all as follows:\n\n.. code::\n\n pytest test --hawq://username:password@hostname:port/database\n\nRun only the standard SQLAlchemy test suite:\n\n.. code::\n\n pytest test --hawq://username:password@hostname:port/database --sqla-only\n\nRun only the custom sqlalchemy_hawq tests:\n\n.. code::\n\n pytest test --hawq://username:password@hostname:port/database --custom-only\n\nRun only the custom tests that don't require a live db connection - e.g., for ci:\n\n.. code::\n\n pytest test --offline-only\n\nFor tests that use a live db connection, user running the tests must be able to create and drop tables on the db provided. Also, many of the tests require that there are pre-existing schemas 'test_schema' and 'test_schema_2' on the db. The test suite can be run without them but the tests will fail.\n\nSee https://github.com/zzzeek/sqlalchemy/blob/master/README.unittests.rst and https://github.com/zzzeek/sqlalchemy/blob/master/README.dialects.rst for more information on test configuration. Note that no default db url is stored in sqlalchemy_hawq's setup.cfg.\n\nDeploy (For developers)\n^^^^^^^^^^^^^^^^^^^^^^^\n\nCreate the venv and ensure the latest versions of setuptools and pip are installed:\n\n.. code::\n\n python3 -m venv venv\n source venv/bin/activate\n pip install -U setuptools pip\n\nInstall sqlalchemy_hawq for deployment and create the distribution packages:\n\n.. code::\n\n pip install .[deploy]\n python setup.py install sdist bdist_wheel\n\nIf you want, you can now check for any problems in the distribution files:\n\n.. code::\n\n twine check dist/*\n\nThen:\n\n.. code::\n\n twine upload dist/* --repository-url http://pyshop.bcgsc.ca/simple/\n\n----\n\nUsing in a SQLAlchemy project\n-----------------------------\n\nHow to incorporate sqlalchemy-hawq\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\nAdd sqlalchemy_hawq to your dependencies and install.\n\n.. code::\n\n pip install sqlalchemy_hawq\n\nThen the plugin can be used like any other engine\n\n.. code::\n\n from sqlalchemy import create_engine\n\n engine = create_engine('hawq://USERNAME:PASSWORD@hdp-master02.hadoop.bcgsc.ca:5432/test_refactor/')\n\nFor sqlalchemy's instructions on how to use their engine, see https://docs.sqlalchemy.org/en/13/core/engines.html.\n\nHawq-specific table arguments\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\nHawq specific table arguments are also supported (Not all features are supported yet)\n\n.. list-table::\n :header-rows: 1\n\n * - Argument\n - Type\n - Example\n - Notes\n * - hawq_distributed_by\n - str\n - ``'column_name'``\n - \n * - hawq_partition_by\n - RangePartition or ListPartition\n - ``ListPartition('chrom', {'chr1': '1', 'chr2':'2', 'chr3':'3'}, [RangeSubpartition('year', 2002, 2012, 1), RangeSubpartition('month', 1, 13, 1),])``\n - Does not currently support range partitioning on dates\n * - hawq_apppendonly\n - bool\n - ``True``\n - \n * - hawq_orientation\n - str\n - ``'ROW'``\n - expects one of ``{'ROW', 'PARQUET'}``\n * - hawq_compresstype\n - str\n - ``'ZLIB'``\n - expects one of ``{'ZLIB', 'SNAPPY', 'GZIP', 'NONE'}``\n * - hawq_compresslevel\n - int\n - ``0``\n - expects an integer between 0-9\n * - hawq_bucketnum\n - int\n - ``6``\n - expects an integer between 0 and ``default_hash_table_bucket_number``\n\n\nExample of hawq table arguments with declarative syntax\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\n.. code::\n\n from sqlalchemy.ext.declarative import declarative_base\n from sqlalchemy import Column, Text\n\n Base = declarative_base()\n\n class ExampleTable(Base):\n __tablename__ = 'example_table'\n\n __table_args__ = {\n 'hawq_distributed_by': 'attr1'\n 'hawq_appendonly': 'True'\n }\n\n attr1 = Column(Integer())\n attr2 = Column(Integer())\n\n\n def main():\n engine = create_engine('hawq://USERNAME:PASSWORD@hdp-master02.hadoop.bcgsc.ca:5432/test_refactor/')\n engine.create_all()\n\n----\n\nUsing partitions\n----------------\n\nSee https://hawq.apache.org/docs/userguide/2.3.0.0-incubating/ddl/ddl-partition.html for an extended discussion of how partitions work in Hawq.\n\nBasically, partitioning divides a table into several smaller tables on the value of one or more columns, in order to reduce search time on those columns. The parent table can then be queried/added to without any further reference to the partitions, as Hawq handles all the parent-partition interactions.\n\nPartition arguments are:\n\n.. code::\n\n RangePartition(\n column_name=str,\n start=int,\n end=int,\n every=int,\n subpartitions=[])\n\nor\n\n.. code::\n\n ListPartition(\n column_name=str,\n columns=dict{name_of_partition:value_to_partition_on},\n subpartitions=[])\n\nwhere 'subpartitions' is an array of RangeSubpartitions and/or ListSubpartitions.\n\nSubpartition arguments are\n\n.. code::\n\n RangeSubpartition(\n column_name=str,\n start=int,\n end=int,\n every=int)\n\nor\n\n.. code::\n\n ListSubpartition(\n column_name=str,\n columns=dict{name_of_partition:value_to_partition_on})\n\nNote that the params are the same for the Subpartitions are for the Partitions, except that Subpartitions do not have a nested subpartition array.\n\nPartition level is determined by the order of the subpartitions in the subpartition array.\n\nUsing sqlalchemy-hawq syntax to define a partition:\n\n.. code::\n\n class MockTable(base):\n __tablename__ = 'MockTable'\n __table_args__ = {\n 'hawq_partition_by': RangePartition(\n 'year',\n 2009,\n 2012,\n 1,\n [\n RangeSubpartition(\n 'quarter',\n 1,\n 5,\n 1),\n ListSubpartition(\n 'chrom',\n {\n 'chr1': '1',\n 'chr2': '2',\n 'chr3': '3'}),\n ],\n )\n }\n id = Column('id', Integer(), primary_key=True, autoincrement=False)\n year = Column('year', Integer())\n quarter = Column('quarter', Integer())\n chrom = Column('chrom', Text())\n\nThe SQL output:\n\n.. code::\n\n '''CREATE TABLE \"MockTable\" (\n id INTEGER NOT NULL,\n year INTEGER,\n quarter INTEGER,\n chrom TEXT\n )\n PARTITION BY RANGE (year)\n SUBPARTITION BY RANGE (quarter)\n SUBPARTITION TEMPLATE\n (\n START (1) END (5) EVERY (1),\n DEFAULT SUBPARTITION extra\n )\n SUBPARTITION BY LIST (chrom)\n SUBPARTITION TEMPLATE\n (\n SUBPARTITION chr1 VALUES ('1'),\n SUBPARTITION chr2 VALUES ('2'),\n SUBPARTITION chr3 VALUES ('3'),\n DEFAULT SUBPARTITION other\n )\n (\n START (2009) END (2012) EVERY (2),\n DEFAULT PARTITION extra\n )'''\n\nThe resulting tables:\n\n.. code::\n\n test_refactor=> \\dt\n List of relations\n Schema | Name | Type | Owner\n --------+-----------------------------------------------+-------+---------\n public | MockTable | table | elewis\n public | MockTable_1_prt_2 | table | elewis\n public | MockTable_1_prt_2_2_prt_2 | table | elewis\n public | MockTable_1_prt_2_2_prt_2_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_2_2_prt_2_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_2_2_prt_2_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_2_2_prt_2_3_prt_other | table | elewis\n public | MockTable_1_prt_2_2_prt_3 | table | elewis\n public | MockTable_1_prt_2_2_prt_3_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_2_2_prt_3_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_2_2_prt_3_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_2_2_prt_3_3_prt_other | table | elewis\n public | MockTable_1_prt_2_2_prt_4 | table | elewis\n public | MockTable_1_prt_2_2_prt_4_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_2_2_prt_4_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_2_2_prt_4_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_2_2_prt_4_3_prt_other | table | elewis\n public | MockTable_1_prt_2_2_prt_5 | table | elewis\n public | MockTable_1_prt_2_2_prt_5_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_2_2_prt_5_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_2_2_prt_5_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_2_2_prt_5_3_prt_other | table | elewis\n public | MockTable_1_prt_2_2_prt_extra | table | elewis\n public | MockTable_1_prt_2_2_prt_extra_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_2_2_prt_extra_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_2_2_prt_extra_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_2_2_prt_extra_3_prt_other | table | elewis\n public | MockTable_1_prt_3 | table | elewis\n public | MockTable_1_prt_3_2_prt_2 | table | elewis\n public | MockTable_1_prt_3_2_prt_2_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_3_2_prt_2_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_3_2_prt_2_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_3_2_prt_2_3_prt_other | table | elewis\n public | MockTable_1_prt_3_2_prt_3 | table | elewis\n public | MockTable_1_prt_3_2_prt_3_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_3_2_prt_3_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_3_2_prt_3_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_3_2_prt_3_3_prt_other | table | elewis\n public | MockTable_1_prt_3_2_prt_4 | table | elewis\n public | MockTable_1_prt_3_2_prt_4_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_3_2_prt_4_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_3_2_prt_4_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_3_2_prt_4_3_prt_other | table | elewis\n public | MockTable_1_prt_3_2_prt_5 | table | elewis\n public | MockTable_1_prt_3_2_prt_5_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_3_2_prt_5_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_3_2_prt_5_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_3_2_prt_5_3_prt_other | table | elewis\n public | MockTable_1_prt_3_2_prt_extra | table | elewis\n public | MockTable_1_prt_3_2_prt_extra_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_3_2_prt_extra_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_3_2_prt_extra_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_3_2_prt_extra_3_prt_other | table | elewis\n public | MockTable_1_prt_extra | table | elewis\n public | MockTable_1_prt_extra_2_prt_2 | table | elewis\n public | MockTable_1_prt_extra_2_prt_2_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_extra_2_prt_2_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_extra_2_prt_2_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_extra_2_prt_2_3_prt_other | table | elewis\n public | MockTable_1_prt_extra_2_prt_3 | table | elewis\n public | MockTable_1_prt_extra_2_prt_3_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_extra_2_prt_3_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_extra_2_prt_3_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_extra_2_prt_3_3_prt_other | table | elewis\n public | MockTable_1_prt_extra_2_prt_4 | table | elewis\n public | MockTable_1_prt_extra_2_prt_4_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_extra_2_prt_4_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_extra_2_prt_4_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_extra_2_prt_4_3_prt_other | table | elewis\n public | MockTable_1_prt_extra_2_prt_5 | table | elewis\n public | MockTable_1_prt_extra_2_prt_5_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_extra_2_prt_5_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_extra_2_prt_5_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_extra_2_prt_5_3_prt_other | table | elewis\n public | MockTable_1_prt_extra_2_prt_extra | table | elewis\n public | MockTable_1_prt_extra_2_prt_extra_3_prt_chr1 | table | elewis\n public | MockTable_1_prt_extra_2_prt_extra_3_prt_chr2 | table | elewis\n public | MockTable_1_prt_extra_2_prt_extra_3_prt_chr3 | table | elewis\n public | MockTable_1_prt_extra_2_prt_extra_3_prt_other | table | elewis\n\n\n", "description_content_type": "text/x-rst", "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://github.com/bcgsc/sqlalchemy_hawq", "keywords": "", "license": "", "maintainer": "", "maintainer_email": "", "name": "sqlalchemy-hawq", "package_url": "https://pypi.org/project/sqlalchemy-hawq/", "platform": "", "project_url": "https://pypi.org/project/sqlalchemy-hawq/", "project_urls": { "Homepage": "https://github.com/bcgsc/sqlalchemy_hawq" }, "release_url": "https://pypi.org/project/sqlalchemy-hawq/1.0.1/", "requires_dist": [ "sqlalchemy (>=1.2.12[postgresql])", "psycopg2-binary", "twine ; extra == 'deploy'", "wheel ; extra == 'deploy'", "m2r ; extra == 'deploy'", "pytest (>=4.4.0) ; extra == 'dev'", "pytest-cov ; extra == 'dev'", "mock ; extra == 'dev'", "pytest-xdist ; extra == 'dev'", "twine ; extra == 'dev'", "wheel ; extra == 'dev'", "m2r ; extra == 'dev'", "black ; extra == 'dev'", "flake8 ; extra == 'dev'", "pytest (>=4.4.0) ; extra == 'test'", "pytest-cov ; extra == 'test'", "mock ; extra == 'test'", "pytest-xdist ; extra == 'test'" ], "requires_python": ">=3.6", "summary": "", "version": "1.0.1" }, "last_serial": 5942051, "releases": { "1.0.1": [ { "comment_text": "", "digests": { "md5": "f11731724fc59b3b944b65c3d779a457", "sha256": "d6eb15a15adf70f63d5fa93b118d06d61f5ed99a015a604ca27378ad9477ad0a" }, "downloads": -1, "filename": "sqlalchemy_hawq-1.0.1-py3.7.egg", "has_sig": false, "md5_digest": "f11731724fc59b3b944b65c3d779a457", "packagetype": "bdist_egg", "python_version": "3.7", "requires_python": ">=3.6", "size": 40556, "upload_time": "2019-10-07T23:59:16", "url": "https://files.pythonhosted.org/packages/ed/b0/6e7fa019020fa88ddeda0eff791b53f00a4311247ef2be34f1238695b33d/sqlalchemy_hawq-1.0.1-py3.7.egg" }, { "comment_text": "", "digests": { "md5": "d6af9332db36c38d10882fe55831148a", "sha256": "1073dda5a36ff073ee15f723e6a192f5640458b5a383c410948ff1ceb80f3523" }, "downloads": -1, "filename": "sqlalchemy_hawq-1.0.1-py3-none-any.whl", "has_sig": false, "md5_digest": "d6af9332db36c38d10882fe55831148a", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 19661, "upload_time": "2019-10-07T23:59:14", "url": "https://files.pythonhosted.org/packages/cb/61/4653a913dc450b2d38cb80a847e9bbe0ce879794fd7b7672059e56a03e05/sqlalchemy_hawq-1.0.1-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "256ada3efed28bcacd10ba6d15f1c1d5", "sha256": "d861b08bcfb89d3c66e6eaab078aee584bd9443c89bb7c145dcad1e092a66e08" }, "downloads": -1, "filename": "sqlalchemy_hawq-1.0.1.tar.gz", "has_sig": false, "md5_digest": "256ada3efed28bcacd10ba6d15f1c1d5", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 19571, "upload_time": "2019-10-07T23:59:18", "url": "https://files.pythonhosted.org/packages/85/00/89e8837c416a324ca7174b6f353f5a909de40ba0199ddad2dea15ba989a1/sqlalchemy_hawq-1.0.1.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "f11731724fc59b3b944b65c3d779a457", "sha256": "d6eb15a15adf70f63d5fa93b118d06d61f5ed99a015a604ca27378ad9477ad0a" }, "downloads": -1, "filename": "sqlalchemy_hawq-1.0.1-py3.7.egg", "has_sig": false, "md5_digest": "f11731724fc59b3b944b65c3d779a457", "packagetype": "bdist_egg", "python_version": "3.7", "requires_python": ">=3.6", "size": 40556, "upload_time": "2019-10-07T23:59:16", "url": "https://files.pythonhosted.org/packages/ed/b0/6e7fa019020fa88ddeda0eff791b53f00a4311247ef2be34f1238695b33d/sqlalchemy_hawq-1.0.1-py3.7.egg" }, { "comment_text": "", "digests": { "md5": "d6af9332db36c38d10882fe55831148a", "sha256": "1073dda5a36ff073ee15f723e6a192f5640458b5a383c410948ff1ceb80f3523" }, "downloads": -1, "filename": "sqlalchemy_hawq-1.0.1-py3-none-any.whl", "has_sig": false, "md5_digest": "d6af9332db36c38d10882fe55831148a", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 19661, "upload_time": "2019-10-07T23:59:14", "url": "https://files.pythonhosted.org/packages/cb/61/4653a913dc450b2d38cb80a847e9bbe0ce879794fd7b7672059e56a03e05/sqlalchemy_hawq-1.0.1-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "256ada3efed28bcacd10ba6d15f1c1d5", "sha256": "d861b08bcfb89d3c66e6eaab078aee584bd9443c89bb7c145dcad1e092a66e08" }, "downloads": -1, "filename": "sqlalchemy_hawq-1.0.1.tar.gz", "has_sig": false, "md5_digest": "256ada3efed28bcacd10ba6d15f1c1d5", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 19571, "upload_time": "2019-10-07T23:59:18", "url": "https://files.pythonhosted.org/packages/85/00/89e8837c416a324ca7174b6f353f5a909de40ba0199ddad2dea15ba989a1/sqlalchemy_hawq-1.0.1.tar.gz" } ] }