def test_dataframe_set_returns(self):
from decimal import Decimal
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SET SCRIPT
foo(%s)
RETURNS DECIMAL(10,5) AS
import numpy as np
def run(ctx):
df = ctx.get_dataframe(num_rows="all")
return np.asscalar(df.iloc[:, 0].sum())
/
''' % (self.col_defs)))
rows = self.query('SELECT foo(%s) FROM FN2.TEST1' % (self.col_names))
self.assertRowsEqual([(Decimal(self.num_rows), )], rows)
def test_imported_scripts_are_cached(self):
self.query(
udf.fixindent('''
CREATE r SCALAR SCRIPT
foo()
RETURNS BOOLEAN AS
run <- function(ctx) {
a <- exa$import_script('bottom')
b <- exa$import_script('bottom')
identical(a, b)
}
/
'''))
rows = self.query('SELECT foo() FROM DUAL')
self.assertRowsEqual([(True, )], rows)
def test_interleaving_sleeps(self):
self.query(
udf.fixindent('''
create or replace lua scalar script
wait_n_echo(v number, t number)
returns number as
local socket = require "socket"
function run (ctx)
socket.sleep(ctx.t)
return math.random(ctx.v)
end
'''))
rows = self.query(
'''select sum(s) from (select sum(wait_n_echo(1,x)) s from (values 1,2,3) as t(x) group by x)'''
)
self.assertEqual(3, rows[0][0])
def setUp(self):
self.query('DROP SCHEMA FN2 CASCADE', ignore_errors=True)
self.query('DROP SCHEMA FN3 CASCADE', ignore_errors=True)
self.query('CREATE SCHEMA FN2')
self.query('CREATE SCHEMA FN3')
self.query('OPEN SCHEMA FN2')
self.query(udf.fixindent('''
CREATE python3 SCALAR SCRIPT
bottom()
RETURNS INT AS
def f():
return 42
/
'''))
def test_dataframe_scalar_emits_no_iter(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SCALAR SCRIPT
foo(%s)
EMITS(%s) AS
def run(ctx):
df = ctx.get_dataframe()
df = ctx.get_dataframe()
df = ctx.get_dataframe()
ctx.emit(df)
/
''' % (self.col_defs, self.col_defs)))
rows = self.query('SELECT foo(%s) FROM FN2.TEST1' % (self.col_names))
self.assertRowsEqual([self.col_tuple] * self.num_rows, rows)
def test_import_works(self):
self.query(
udf.fixindent('''
CREATE r SCALAR SCRIPT
foo()
RETURNS INT AS
b <- exa$import_script('bottom')
run <- function(ctx) {
b$f()
}
/
'''))
rows = self.query('SELECT foo() FROM DUAL')
self.assertRowsEqual([(42, )], rows)
def setUp(self):
self.clean()
self.query('create schema row_level_security_data', ignore_errors=True)
self.query('create schema row_level_security_adapter',
ignore_errors=True)
self.createUser("u1", "u1")
self.createUser("u2", "u2")
self.query(
udf.fixindent('''
grant create session to u1;
'''))
self.query(
udf.fixindent('''
grant create session to u2;
'''))
self.query(
udf.fixindent('''
create or replace table row_level_security_adapter.user_pref(username varchar(100), wants_only_active bool);
'''))
self.query(
udf.fixindent('''
insert into
row_level_security_adapter.user_pref
values
('SYS', false),
('U1', true),
('U2', false);
'''))
self.query(
udf.fixindent('''
CREATE OR REPLACE TABLE row_level_security_data.t(a1 varchar(100), a2 varchar(100), userName varchar(100), active bool);
'''))
self.query(
udf.fixindent('''
INSERT INTO
row_level_security_data.t
values
('a', 'b', 'SYS', true),
('c', 'd', 'SYS', false),
('e', 'f', 'U2', true),
('g', 'h', 'U2', false),
('i', 'j', 'U1', true),
('k', 'l', 'U1', false);
'''))
self.query(
udf.fixindent('''
create or replace connection sys_connection to 'ws://%s' user '%s' identified by '%s'
''' % (self.connection, "sys", "exasol")))
self.query("commit")
def test_import_works_with_default_name(self):
self.query(
udf.fixindent('''
CREATE lua SCALAR SCRIPT
foo()
RETURNS DOUBLE AS
exa.import('bottom')
function run(ctx)
return bottom.f()
end
/
'''))
rows = self.query('SELECT foo() FROM DUAL')
self.assertRowsEqual([(42, )], rows)
def test_dataframe_set_emits_empty(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SET SCRIPT
foo(%s)
EMITS(%s) AS
import pandas as pd
def run(ctx):
df = pd.DataFrame()
ctx.emit(df)
/
''' % (self.col_defs, self.col_defs)))
with self.assertRaisesRegexp(Exception, 'emit DataFrame is empty'):
rows = self.query('SELECT foo(%s) FROM FN2.TEST1' %
(self.col_names))
def test_stdlib_modules_are_importable(self, module):
sql = udf.fixindent('''
CREATE OR REPLACE python3 SCALAR SCRIPT
i()
RETURNS VARCHAR(1024) AS
import %(module)s
def run(ctx):
try:
return %(module)s.__file__
except AttributeError:
return "(built-in)"
''')
self.query(sql % {'module': module})
self.query('SELECT i() FROM DUAL')
def test_imported_scripts_are_cached(self):
self.query(
udf.fixindent('''
CREATE lua SCALAR SCRIPT
foo()
RETURNS BOOLEAN AS
function run(ctx):
exa.import('bottom', 'a')
exa.import('bottom', 'b')
return a === b
/
'''))
rows = self.query('SELECT foo() FROM DUAL')
self.assertRowsEqual([(True, )], rows)
def test_dataframe_set_emits_null_start_col(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SET SCRIPT
foo(%s)
EMITS(%s) AS
def run(ctx):
df = ctx.get_dataframe(num_rows='all', start_col=5)
ctx.emit(df)
/
''' % (self.col_defs, ', '.join(self.col_defs.split(', ')[5:]))))
rows = self.query('SELECT foo(%s) FROM FN2.TEST2' % (self.col_names))
self.assertRowsEqual([
self.col_tuple_1[5:], self.col_tuple_2[5:], self.col_tuple_null[5:]
], rows)
def test_import_works_with_qualified_names(self, schema, name):
self.query('OPEN SCHEMA %s' % schema)
self.query(
udf.fixindent('''
CREATE lua SCALAR SCRIPT
foo()
RETURNS DOUBLE AS
import('%s', 'b')
function run(ctx)
return b.f()
end
/
''' % name))
rows = self.query('SELECT foo() FROM DUAL')
self.assertRowsEqual([(42, )], rows)
def setUp(self):
self.query('DROP SCHEMA FN2 CASCADE', ignore_errors=True)
self.query('DROP SCHEMA FN3 CASCADE', ignore_errors=True)
self.query('CREATE SCHEMA FN2')
self.query('CREATE SCHEMA FN3')
self.query('OPEN SCHEMA FN2')
self.query(udf.fixindent('''
CREATE EXTERNAL SCALAR SCRIPT
bottom()
RETURNS INT AS
# redirector @@redirector_url@@
def f():
return 42
/
'''))
def test_dataframe_set_emits_wrong_args7(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SET SCRIPT
foo(%s)
EMITS(%s) AS
def run(ctx):
df = ctx.get_dataframe(num_rows="all")
df = df.iloc[:, 1:]
ctx.emit(df)
/
''' % (self.col_defs, self.col_defs)))
with self.assertRaisesRegexp(
Exception, 'emit\(\) takes exactly 11 arguments \(10 given\)'):
rows = self.query('SELECT foo(%s) FROM FN2.TEST1' %
(self.col_names))
def test_assertion_in_cleanup_is_propagated(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE lua SCALAR SCRIPT
foo()
RETURNS DOUBLE AS
function run(ctx)
return 42
end
function cleanup()
assert(1 == 4711)
end
'''))
with self.assertRaisesRegexp(Exception, 'assertion failed'):
self.query('SELECT foo() FROM dual')
def createJdbcAdapter(self,
schemaName="ADAPTER",
adapterName="JDBC_ADAPTER"):
self.dropOldAdapter(schemaName, adapterName)
self.query('CREATE SCHEMA {schema}'.format(schema=schemaName))
self.query(
udf.fixindent('''
CREATE OR REPLACE JAVA ADAPTER SCRIPT {adapter} AS
%scriptclass com.exasol.adapter.jdbc.JdbcAdapter;
%jvmoption -Xms64m -Xmx64m;
%jar {jdbc_path};
%jar /buckets/bfsdefault/jdbc-adapter/virtualschema-jdbc-adapter.jar;
/
''').format(
adapter=adapterName,
jdbc_path='/buckets/bfsdefault/jdbc-adapter/exajdbc.jar')
) #udf.opts.jdbc_path)) # /buckets/internal/jdbc/EXASOL/exajdbc.jar
def test_dataframe_set_emits_null_start_col_too_large(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SET SCRIPT
foo(%s)
EMITS(%s) AS
def run(ctx):
df = ctx.get_dataframe(num_rows='all', start_col=100000)
ctx.emit(df)
/
''' % (self.col_defs, self.col_defs)))
with self.assertRaisesRegexp(
Exception, "is 100000, but there are only %d input columns" %
len(self.col_names.split(', '))):
rows = self.query('SELECT foo(%s) FROM FN2.TEST2' %
(self.col_names))
def setUp(self):
self.query('create schema sup7256', ignore_errors=True)
self.query(
udf.fixindent('''
CREATE OR REPLACE python3 scalar SCRIPT pandas_babies(x int)
returns int as
import pandas as pd
def run(ctx):
# This is just some stuff from the pandas tutorial
# The inital set of baby names and bith rates
names = ['Bob','Jessica','Mary','John','Mel']
births = [968, 155, 77, 578, 973]
BabyDataSet = list(zip(names,births))
df = pd.DataFrame(data = BabyDataSet, columns=['Names', 'Births'])
return len(df)
/
'''))
def test_jar_path2(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE java SCALAR SCRIPT
test_jar_path2()
RETURNS int AS
%jar
class TEST_JAR_PATH2 {
static int run(ExaMetadata exa, ExaIterator ctx) throws Exception {
X.run(exa, ctx);
return 42;
}
}
'''))
with self.assertRaisesRegexp(Exception,
'End of %jar statement not found'):
rows = self.query('SELECT test_jar_path2() FROM dual')
def test_secure_websocket_api_connection(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON SCALAR SCRIPT websocket_api.connect_secure() returns int AS
import EXASOL
import ssl
import os
def run(ctx):
os.environ["USER"]="******"
with EXASOL.connect('wss://%s', '%s', '%s', sslopt={"cert_reqs": ssl.CERT_NONE}) as connection:
with connection.cursor() as cursor:
cursor.execute('SELECT 1 FROM dual')
for row in cursor:
pass
/
''' % (self.connection, self.user, self.pwd)))
self.query('''SELECT websocket_api.connect_secure() FROM dual''')
def test_jvm_opt_multiple_opts2(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE java SCALAR SCRIPT
test_jvm_opt_multiple_opts2()
RETURNS int AS
%jvmoption -Xmx5000m;
%jvmoption -Xms56m -Xmx128m -Xss1k;
%jvmoption -Xss512k -Xms128m;
class TEST_JVM_OPT_MULTIPLE_OPTS2 {
static int run(ExaMetadata exa, ExaIterator ctx) throws Exception {
return 1;
}
}
'''))
rows = self.query('SELECT test_jvm_opt_multiple_opts2() FROM DUAL')
self.assertEqual(1, rows[0][0])
def test_main_is_not_executed_at_creation_time(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE java SCALAR SCRIPT
main_error()
RETURNS int AS
class MAIN_ERROR {
static void main(String[] args) {
throw new RuntimeException("Main Executed");
}
static int run(ExaMetadata exa, ExaIterator ctx) {
ctx.emit(42);
}
}
/
'''))
def test_dataframe_set_emits_wrong_args0(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SET SCRIPT
foo(%s)
EMITS(%s) AS
import pandas as pd
def run(ctx):
df = pd.DataFrame([[]])
ctx.emit(df)
/
''' % (self.col_defs, self.col_defs)))
with self.assertRaisesRegexp(
Exception, 'emit\(\) takes exactly 11 arguments \(0 given\)'):
rows = self.query('SELECT foo(%s) FROM FN2.TEST1' %
(self.col_names))
def test_jvm_opt3(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE java SCALAR SCRIPT
test_jvm_opt3()
RETURNS int AS
%jvmoption ;
class TEST_JVM_OPT3 {
static int run(ExaMetadata exa, ExaIterator ctx) throws Exception {
X.run(exa, ctx);
return 42;
}
}
'''))
with self.assertRaisesRegexp(
Exception, 'No values found for %jvmoption statement'):
rows = self.query('SELECT test_jvm_opt3() FROM dual')
def test_exception_in_init_is_propagated(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE java SCALAR SCRIPT
foo()
RETURNS INT AS
class FOO {
static int run(ExaMetadata exa, ExaIterator ctx) {
return 42;
}
static int init(ExaMetadata exa) throws Exception {
throw new Exception("4711");
}
}
'''))
with self.assertRaisesRegexp(Exception, '4711'):
self.query('SELECT foo() FROM dual')
def test_jvm_opt_invalid_opt3(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE java SCALAR SCRIPT
test_jvm_opt_invalid_opt3()
RETURNS int AS
%jvmoption -Xjunk;
%jvmoption -Xmx56m;
class TEST_JVM_OPT_INVALID_OPT3 {
static int run(ExaMetadata exa, ExaIterator ctx) throws Exception {
return 1;
}
}
'''))
with self.assertRaisesRegexp(
Exception, '.*Cannot start the JVM: unknown error.*'):
rows = self.query('SELECT test_jvm_opt_invalid_opt3() FROM dual')
def test_dataframe_set_emits_iter(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE PYTHON3 SET SCRIPT
foo(%s)
EMITS(%s) AS
def run(ctx):
while True:
df = ctx.get_dataframe(num_rows=1)
if df is None:
break
ctx.emit(df)
/
''' % (self.col_defs, self.col_defs)))
rows = self.query('SELECT foo(%s) FROM FN2.TEST1' % (self.col_names))
self.assertRowsEqual([self.col_tuple] * self.num_rows, rows)
def test_ImportError_is_catchable(self):
self.query(
udf.fixindent('''
CREATE OR REPLACE python SCALAR SCRIPT
catch_import_error()
RETURNS int AS
try:
import unknown_module
except ImportError:
pass
def run(ctx):
return 42
'''))
rows = self.query('SELECT catch_import_error() FROM dual')
self.assertRowsEqual([(42, )], rows)
def test_import_works_with_qualified_names(self, schema, name):
self.query('OPEN SCHEMA %s' % schema)
self.query(
udf.fixindent('''
CREATE r SCALAR SCRIPT
foo()
RETURNS INT AS
b <- exa$import_script('%s')
run <- function(ctx) {
b$f()
}
/
''' % name))
rows = self.query('SELECT foo() FROM DUAL')
self.assertRowsEqual([(42, )], rows)