Beispiel #1
0
def test_million_row():
    conn = create_conn()
    tablename = 'pymcsapi_millionrow'
    drop_table(conn, tablename)
    exec_stmt(conn,
              'CREATE TABLE %s (a int, b int) engine=columnstore' % tablename)

    d = pymcsapi.ColumnStoreDriver()
    b = d.createBulkInsert(DB_NAME, tablename, 0, 0)
    rows = 1000000
    try:
        for i in range(0, rows):
            b.setColumn(0, i)
            b.setColumn(1, rows - i)
            b.writeRow()
        b.commit()
    except RuntimeError as err:
        b.rollback()
        pytest.fail("Error executing bulk insert: %s" % (err, ))

    try:
        cursor = conn.cursor()
        cursor.execute('select count(*) cnt from %s' % tablename)
        row = cursor.fetchone()
        assert row is not None
        assert row[0] == rows
    except mariadb.Error as err:
        pytest.fail("Error executing query: %s, error: %s" %
                    (query_all_types, err))
    finally:
        if cursor: cursor.close()

    drop_table(conn, tablename)
    conn.close()
Beispiel #2
0
def test_list_table_locks():
    conn = create_conn()
    tablename1 = 'pymcsapi_lock1'
    tablename2 = 'pymcsapi_lock2'
    drop_table(conn, tablename1)
    drop_table(conn, tablename2)
    exec_stmt(conn, 'CREATE TABLE %s (a int, b int) engine=columnstore' % tablename1)
    exec_stmt(conn, 'CREATE TABLE %s (a int, b int) engine=columnstore' % tablename2)
   
    d = pymcsapi.ColumnStoreDriver()

    try:
        lockedTablesAtBeginning = len(d.listTableLocks())
        b1 = d.createBulkInsert(DB_NAME, tablename1, 0, 0)
        # verify that one additional table is locked
        assert 1 == (len(d.listTableLocks()) - lockedTablesAtBeginning)
        b2 = d.createBulkInsert(DB_NAME, tablename2, 0, 0)
        # verify that two additional tables are locked
        assert 2 == (len(d.listTableLocks()) - lockedTablesAtBeginning)
        b2.rollback()
        # verify that one additional table is locked
        assert 1 == (len(d.listTableLocks()) - lockedTablesAtBeginning)
        b1.rollback()
        assert 0 == (len(d.listTableLocks()) - lockedTablesAtBeginning)
    except RuntimeError as err:
        pytest.fail("Error executing driver operation: %s" % (err,)) 
 
    try:
        drop_table(conn, tablename1)
        drop_table(conn, tablename2)
    except mariadb.Error as err:
        pytest.fail("Error during cleanup: %s" %(err,))
    finally:  
        conn.close()
Beispiel #3
0
def test_dates():
    # setup / create test table
    conn = create_conn()
    tablename = 'pymcsapi_dates'
    drop_table(conn, tablename)
    exec_stmt(
        conn,
        "create table %s (id int, dt date, dtm datetime) engine=columnstore" %
        (tablename, ))

    # generate and load a range of dates
    d = pymcsapi.ColumnStoreDriver()
    b = d.createBulkInsert('mcsapi', 'pymcsapi_dates', 0, 0)
    maxDays = 366 + 365 + 1
    dt = datetime.datetime(2016, 1, 1, 12, 34, 56)
    try:
        for i in range(1, maxDays):
            b.setColumn(0, i)
            b.setColumn(
                1, pymcsapi.ColumnStoreDateTime(dt.year, dt.month, dt.day))
            b.setColumn(
                2,
                pymcsapi.ColumnStoreDateTime(dt.year, dt.month, dt.day,
                                             dt.hour, dt.minute, dt.second))
            b.writeRow()
            dt += datetime.timedelta(days=1)
        b.commit()
    except RuntimeError as err:
        b.rollback()
        pytest.fail("Error executing bulk insert: %s" % (err, ))

    # query and validate values written
    try:
        cursor = conn.cursor()
        cursor.execute("select id, dt, dtm from pymcsapi_dates order by id")
        exp_i = 1
        exp_dt = datetime.date(2016, 1, 1)
        exp_dtm = datetime.datetime(2016, 1, 1, 12, 34, 56)
        for (id, dt, dtm) in cursor:
            assert id == exp_i
            assert dt == exp_dt
            assert dtm == exp_dtm
            exp_i += 1
            exp_dt += datetime.timedelta(days=1)
            exp_dtm += datetime.timedelta(days=1)
    except mariadb.Error as err:
        pytest.fail("Error executing test_dates validation query: %s" %
                    (err, ))
    finally:
        if cursor: cursor.close()

    drop_table(conn, tablename)
    conn.close()
Beispiel #4
0
def i1_common(datatype, ch_len):
    conn = create_conn()
    tablename = 'pymcsapi_i1'
    drop_table(conn, tablename)
    if (datatype == 'char'):
        exec_stmt(
            conn,
            'create table pymcsapi_i1(i int, ch char(%s)) engine=columnstore' %
            (ch_len, ))
    else:
        exec_stmt(
            conn,
            'create table pymcsapi_i1(i int, ch varchar(%s)) engine=columnstore'
            % (ch_len, ))

    d = pymcsapi.ColumnStoreDriver()
    b = d.createBulkInsert(DB_NAME, tablename, 0, 0)
    try:
        b.setColumn(0, 1)[0].setColumn(1, 'ABC')[0].writeRow()
        b.setColumn(0, 2)[0].setColumn(1, 'A')[0].writeRow()
        b.setColumn(0, 3)[0].setColumn(1, 'XYZ')[0].writeRow()
        b.commit()
    except RuntimeError as err:
        b.rollback()
        pytest.fail("Error executing bulk insert: %s" % (err, ))

    try:
        cursor = conn.cursor()
        cursor.execute('select i, ch from pymcsapi_i1 order by i')
        for (i, ch) in cursor:
            if (i == 1): assert ch == 'ABC'
            elif (i == 2): assert ch == 'A'
            elif (i == 3): assert ch == 'XYZ'
            else: pytest.fail("invalid i value: %s" % (i, ))
    except mariadb.Error as err:
        pytest.fail("Error executing i1_common query: %s" % (err, ))
    finally:
        if cursor: cursor.close()
    drop_table(conn, tablename)
    conn.close()
def test_inserted():
    conn = create_conn()
    tablename = 'py_summary_ins'
    rows = 1000
    drop_table(conn, tablename)
    exec_stmt(
        conn,
        'create table if not exists %s(a int, b varchar(10)) engine=columnstore'
        % (tablename, ))

    d = pymcsapi.ColumnStoreDriver()
    b = d.createBulkInsert(DB_NAME, tablename, 0, 0)
    try:
        for i in range(0, rows):
            bulk, status = b.setColumn(0, i)
            assert status == pymcsapi.CONVERT_STATUS_NONE
            bulk, status = b.setColumn(1, 'ABC')
            assert status == pymcsapi.CONVERT_STATUS_NONE
            bulk.writeRow()
        b.commit()

        s = b.getSummary()
        assert int(s.getRowsInsertedCount()) == rows
    except RuntimeError as err:
        b.rollback()
        pytest.fail("Error executing status row insertion test: %s" % (err, ))

    try:
        cursor = conn.cursor()
        cursor.execute('select count(*) cnt from %s' % (tablename, ))
        row = cursor.fetchone()
        assert row is not None
        assert row[0] == rows
    except mariadb.Error as err:
        pytest.fail("Error executing status row insertion query: %s" % (err, ))
    finally:
        if cursor: cursor.close()
    drop_table(conn, tablename)
    conn.close()
def test_truncated():
    conn = create_conn()
    tablename = 'py_summary_tru'
    drop_table(conn, tablename)
    exec_stmt(
        conn,
        'create table if not exists %s(a int, b varchar(10)) engine=columnstore'
        % (tablename, ))

    d = pymcsapi.ColumnStoreDriver()
    b = d.createBulkInsert(DB_NAME, tablename, 0, 0)
    try:
        bulk, status = b.setColumn(0, 23)
        assert status == pymcsapi.CONVERT_STATUS_NONE
        bulk, status = b.setColumn(1, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
        assert status == pymcsapi.CONVERT_STATUS_TRUNCATED
        bulk.writeRow()
        b.commit()

        s = b.getSummary()
        assert int(s.getTruncationCount()) == 1
    except RuntimeError as err:
        pytest.fail("Error executing status row truncation test: %s" % (err, ))

    try:
        cursor = conn.cursor()
        cursor.execute('select count(*) cnt from %s' % (tablename, ))
        row = cursor.fetchone()
        assert row is not None
        assert row[0] == 1
    except mariadb.Error as err:
        pytest.fail("Error executing status row truncation query: %s" %
                    (err, ))
    finally:
        if cursor: cursor.close()
    drop_table(conn, tablename)
    conn.close()
Beispiel #7
0
def test_is_table_locked():
    conn = create_conn()
    tablename = 'pymcsapi_lock3'
    drop_table(conn, tablename)
    exec_stmt(conn, 'CREATE TABLE %s (a int, b int) engine=columnstore' % tablename)
   
    d = pymcsapi.ColumnStoreDriver()

    try:
        # verify that table is locked
        b = d.createBulkInsert(DB_NAME, tablename, 0, 0)
        assert True == d.isTableLocked(DB_NAME, tablename)
        # verify that table is not locked after rollback
        b.rollback()
        assert False == d.isTableLocked(DB_NAME, tablename)
    except RuntimeError as err:
        pytest.fail("Error executing driver operation: %s" % (err,)) 
 
    try:
        drop_table(conn, tablename)
    except mariadb.Error as err:
        pytest.fail("Error during cleanup: %s" %(err,))
    finally:  
        conn.close()
Beispiel #8
0
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public
# License along with this library; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
# MA 02110-1301  USA

import sys, pymcsapi, csv

if len(sys.argv) != 4:
    print("required arguments: db-name table-name import-file")
    sys.exit(2)
    
driver = pymcsapi.ColumnStoreDriver()
catalog = driver.getSystemCatalog()
table = catalog.getTable(sys.argv[1], sys.argv[2])
columnCount = table.getColumnCount()
bulk = driver.createBulkInsert(sys.argv[1], sys.argv[2], 0, 0)
with open(sys.argv[3], 'rb') as csvfile:
    csvreader = csv.reader(csvfile, delimiter='|')
    for row in csvreader:
        for col in xrange(0, len(row)):
            if col < columnCount:
                bulk.setColumn(col, row[col])
        bulk.writeRow()
bulk.commit()

summary = bulk.getSummary()
print("Execution time: %s" % (summary.getExecutionTime(),))
def export(database, table, df, configuration=None):
    global long
    python2 = True

    if sys.version_info[0] == 3:
        long = int
        python2 = False

    rows = df.collect()
    if configuration == None:
        driver = pymcsapi.ColumnStoreDriver()
    else:
        driver = pymcsapi.ColumnStoreDriver(configuration)
    bulkInsert = driver.createBulkInsert(database, table, 0, 0)
    
    # get the column count of table
    dbCatalog = driver.getSystemCatalog()
    dbTable = dbCatalog.getTable(database, table)
    dbTableColumnCount = dbTable.getColumnCount()
    
    # insert row by row into table
    try:
        for row in rows:
            for columnId in range(0, len(row)):
                if columnId < dbTableColumnCount:
                    if row[columnId] is None:
                        if dbTable.getColumn(columnId).isNullable():
                            bulkInsert.setNull(columnId)
                        else:
                            print("warning: column %d is not nullable. Using default value instead." % (columnId,))
                            bulkInsert.setColumn(columnId, dbTable.getColumn(columnId).getDefaultValue())
                    else:
                        if isinstance(row[columnId], bool):
                            if row[columnId]:
                                bulkInsert.setColumn(columnId, 1)
                            else:
                                bulkInsert.setColumn(columnId, 0)
                    
                        elif isinstance(row[columnId], datetime.date):
                            bulkInsert.setColumn(columnId, row[columnId].strftime('%Y-%m-%d %H:%M:%S'))
                    
                        elif isinstance(row[columnId], decimal.Decimal):
                            dbColumn = dbTable.getColumn(columnId)
                            #DATA_TYPE_DECIMAL, DATA_TYPE_UDECIMAL, DATA_TYPE_FLOAT, DATA_TYPE_UFLOAT, DATA_TYPE_DOUBLE, DATA_TYPE_UDOUBLE
                            if dbColumn.getType() == 4 or dbColumn.getType() == 18 or dbColumn.getType() == 7 or dbColumn.getType() == 21 or dbColumn.getType() == 10 or dbColumn.getType() == 23:
                                s = '{0:f}'.format(row[columnId])
                                bulkInsert.setColumn(columnId, pymcsapi.ColumnStoreDecimal(s))
                            #ANY OTHER DATA TYPE
                            else:
                                bulkInsert.setColumn(columnId, long(row[columnId]))
    
                        #handle python2 unicode strings
                        elif python2 and isinstance(row[columnId], unicode):
                            bulkInsert.setColumn(columnId, row[columnId].encode('utf-8'))

                        #any other datatype is inserted without parsing
                        else:
                            bulkInsert.setColumn(columnId, row[columnId])
            bulkInsert.writeRow()
        bulkInsert.commit()
    except Exception as e:
        bulkInsert.rollback()
        print("An exception occured. The bulk insert was rolled back.")
        print("row: %s, ingest type: %s, ingest value: %s" % (row[columnId], type(row[columnId]), row[columnId]))
        print(type(e), str(e))
       
    #print a short summary of the insertion process
    summary = bulkInsert.getSummary()
    print("Execution time: %s" % (summary.getExecutionTime(),))
    print("Rows inserted: %s" % (summary.getRowsInsertedCount(),))
    print("Truncation count: %s" %(summary.getTruncationCount(),))
    print("Saturated count: %s" %(summary.getSaturatedCount(),))
    print("Invalid count: %s" %(summary.getInvalidCount(),))
Beispiel #10
0
def test_all_types():
    # create connection for table creation / query
    conn = create_conn()

    # create all_types table
    tablename = 'pymcsapi_all_types'
    drop_table(conn, tablename)
    create_all_types = """create table %s (
    uint64 bigint unsigned,
    int64 bigint,
    uint32 int unsigned,
    int32 int,
    uint16 smallint unsigned,
    int16 smallint,
    uint8 tinyint unsigned,
    `int8` tinyint,
    f float,
    d double,
    ch4 char(5),
    vch30 varchar(30),
    dt date,
    dtm datetime,
    dc decimal(18),
    tx text
    ) engine=columnstore""" % (tablename, )
    exec_stmt(conn, create_all_types)

    # load rows into all_types table
    d = pymcsapi.ColumnStoreDriver()
    # simple values
    b = d.createBulkInsert(DB_NAME, tablename, 0, 0)
    try:
        b.setColumn(0, long(1))
        b.setColumn(1, long(2))
        b.setColumn(2, int(3))
        b.setColumn(3, int(4))
        b.setColumn(4, int(5))
        b.setColumn(5, int(6))
        b.setColumn(6, int(7))
        b.setColumn(7, int(8))
        b.setColumn(8, float(1.234))
        b.setColumn(9, float(2.34567))
        b.setColumn(10, 'ABCD')
        b.setColumn(11, 'Hello World')
        b.setColumn(12, pymcsapi.ColumnStoreDateTime(2017, 9, 8))
        b.setColumn(13, pymcsapi.ColumnStoreDateTime(2017, 9, 8, 13, 58, 23))
        b.setColumn(14, pymcsapi.ColumnStoreDecimal(123))
        b.setColumn(15, 'Hello World Longer')
        b.writeRow()

        # min values
        b.setColumn(0, long(0))
        b.setColumn(1, long(-9223372036854775806))
        b.setColumn(2, int(0))
        b.setColumn(3, int(-2147483646))
        b.setColumn(4, int(0))
        b.setColumn(5, int(-32766))
        b.setColumn(6, int(0))
        b.setColumn(7, int(-126))
        b.setColumn(8, float(1.234))
        b.setColumn(9, float(2.34567))
        b.setColumn(10, 'A')
        b.setColumn(11, 'B')
        b.setColumn(12, pymcsapi.ColumnStoreDateTime(1000, 1, 1))
        b.setColumn(13, pymcsapi.ColumnStoreDateTime(1000, 1, 1, 0, 0, 0))
        b.setColumn(14, pymcsapi.ColumnStoreDecimal(-123))
        b.setColumn(15, 'C')
        b.writeRow()

        # max values
        b.setColumn(0, long(9223372036854775807))
        b.setColumn(1, long(9223372036854775807))  # python long is signed
        b.setColumn(2, int(4294967293))
        b.setColumn(3, int(2147483647))
        b.setColumn(4, int(65533))
        b.setColumn(5, int(32767))
        b.setColumn(6, int(253))
        b.setColumn(7, int(127))
        b.setColumn(8, float(1.234))
        b.setColumn(9, float(2.34567))
        b.setColumn(10, 'ZYXW')
        b.setColumn(11, '012345678901234567890123456789')
        b.setColumn(12, pymcsapi.ColumnStoreDateTime(9999, 12, 31))
        b.setColumn(13, pymcsapi.ColumnStoreDateTime(9999, 12, 31, 23, 59, 59))
        b.setColumn(14, pymcsapi.ColumnStoreDecimal(123))
        b.setColumn(15, '012345678901234567890123456789')
        b.writeRow()

        b.commit()
    except RuntimeError as err:
        b.rollback()
        pytest.fail("Error executing bulk insert: %s" % (err, ))

    # verify data
    all_types_validate(
        conn, 1,
        "1, 2, 3, 4, 5, 6, 7, 8, 1.234, 2.34567, ABCD, Hello World, 2017-09-08, 2017-09-08 13:58:23, 123, Hello World Longer"
    )
    all_types_validate(
        conn, 0,
        "0, -9223372036854775806, 0, -2147483646, 0, -32766, 0, -126, 1.234, 2.34567, A, B, 1000-01-01, 1000-01-01 00:00:00, -123, C"
    )
    all_types_validate(
        conn, 9223372036854775807,
        "9223372036854775807, 9223372036854775807, 4294967293, 2147483647, 65533, 32767, 253, 127, 1.234, 2.34567, ZYXW, 012345678901234567890123456789, 9999-12-31, 9999-12-31 23:59:59, 123, 012345678901234567890123456789"
    )

    drop_table(conn, tablename)
    conn.close()
Beispiel #11
0
def test_clear_table_lock():
    conn = create_conn()
    tablename = 'pymcsapi_lock4'
    drop_table(conn, tablename)
    exec_stmt(conn, 'CREATE TABLE %s (a int, b int) engine=columnstore' % tablename)
   
    d = pymcsapi.ColumnStoreDriver()

    try:
        lockedTablesAtBeginning = len(d.listTableLocks())
        # initiate ingestion and verify that tablename is locked
        b1 = d.createBulkInsert(DB_NAME, tablename, 0, 0)
        for i in range(0, 100001):
            b1.setColumn(0, 0)
            b1.setColumn(1, 1)
            b1.writeRow()
        assert True == d.isTableLocked(DB_NAME, tablename)
        d.clearTableLock(DB_NAME, tablename)
        # verify that tablename is not locked after clearTableLock
        assert False == d.isTableLocked(DB_NAME, tablename)
        # use bulk 2 to ingest one row
        b2 = d.createBulkInsert(DB_NAME, tablename, 0, 0)
        b2.setColumn(0, 23)
        b2.setColumn(1, 42)
        b2.writeRow()
        lks = d.listTableLocks()
        b2.commit()
        # verify that tablename is not locked after commit of bulk2
        assert False == d.isTableLocked(DB_NAME, tablename)
        
        # verify that an error is thrown if lock does not exist
        oid = d.getSystemCatalog().getTable(DB_NAME, tablename).getOID()
        lockId = -1
        for l in lks:
            if l.tableOID == oid:
                lockId = l.id
        
        assert lockId > -1
        with pytest.raises(RuntimeError) as exceptionInfo:
            d.clearTableLock(lockId)
        assert exceptionInfo.value.args[0] == "Error, no lock found for lockId: %d" % (lockId,)
        
    except RuntimeError as err:
        pytest.fail("Error caught: %s" % (err,)) 
 
    # verify that only one row was written from bulk2 and bulk1's write was rolled back
    try:
        cursor = conn.cursor()
        cursor.execute('select count(*) cnt from %s' % tablename)
        row = cursor.fetchone()
        assert row is not None
        assert row[0] == 1
        cursor.execute('select b from %s WHERE a=23' % tablename)
        row = cursor.fetchone()
        assert row is not None
        assert row[0] == 42
        drop_table(conn, tablename)
    except mariadb.Error as err:
        pytest.fail("Error executing query: %s" %(err,))
    finally:
        if cursor: cursor.close()
        conn.close()
Beispiel #12
0
def test_exception_thrown():
    with pytest.raises(RuntimeError) as exceptionInfo:
        driver = pymcsapi.ColumnStoreDriver()
        catalog = driver.getSystemCatalog()
        table = catalog.getTable("test", "nonexistent")
    assert exceptionInfo.value.args[0] == 'test.nonexistent not found'