def write_to_ts(args, database):
"""
Writes the database directly to ThoughtSpot.
:param args: The command line arguments.
:param database: The database to write.
:type database: Database
:return: None
"""
rtql = RemoteTQL(hostname=args.to_ts,
username=args.username,
password=args.password)
tempfile = f"{database.database_name}.tmp"
writer = TQLWriter(args.uppercase, args.lowercase, args.camelcase,
args.create_db)
writer.write_tql(database, tempfile)
# The parser expects a file, so create a temp file, parse, then delete.
data = ""
with open(tempfile, "r") as infile:
for line in infile:
data += line
response = rtql.run_tql_command(data)
os.remove(tempfile)
print(response)
def main():
"""Main function for the script."""
args = parse_args()
if valid_args(args):
print(args)
sys.setrecursionlimit(10000) # expanding from the default of 1000. Might cause memory errors.
database = None
worksheet = None
rtql = None
if args.version:
version_path = os.path.dirname(os.path.abspath(__file__))
print(f"convert_ddl (v{VERSION}): {version_path}/convert_ddl")
exit(0) # just exit if printing the version -- similar behavior to help.
if args.debug:
logging.basicConfig(level=logging.DEBUG)
reviewer = DataModelReviewer()
if args.show_tests:
descriptions = reviewer.get_test_descriptions()
print(f"Found {len(descriptions)} tests.")
for test in descriptions.keys():
print("")
print(f"{test}:")
for desc in descriptions[test]:
print(f"\t{desc}")
print("")
# create the database.
if args.ts_ip:
database = read_from_ts(args)
elif args.database_file:
parser = DDLParser(database_name=args.database) # these tests ignore the schema name.
database = parser.parse_ddl(filename=args.database_file)
else: # only continue if there is a database.
exit(0)
# read the worksheet.
# TODO add logic for reading a worksheet.
# create an RTQL object
if args.ts_ip:
rtql = RemoteTQL(hostname=args.ts_ip, username=args.username, password=args.password)
reviewer = DataModelReviewer()
results = reviewer.review_model(database=database, worksheet=worksheet, rtql=rtql)
for test in results.keys():
issues = results[test]
print(f"{test}:")
for issue in issues:
print(f"\t{issue}")
def read_from_ts(args):
"""
Reads the database (from args) from TQL remotely.
:param args: The argument list. Must have the host, database and possibly user/password.
:return: A database that was read.
:rtype: Database
"""
rtql = RemoteTQL(hostname=args.ts_ip, username=args.username, password=args.password)
out = rtql.run_tql_command(f"script database {args.database};")
# The parser expects a file, so create a temp file, parse, then delete.
filename = f"{args.database}.tmp"
with open(filename, "w") as outfile:
for line in out:
outfile.write(line + "\n")
parser = DDLParser(database_name=args.database)
database = parser.parse_ddl(filename=filename)
os.remove(filename)
return database
def test_table_joins(self):
"""Tests the review of the joins between tables in a database."""
parser = DDLParser(database_name="table_join_test")
database = parser.parse_ddl("table_joins.tql")
rtql = RemoteTQL(hostname=TS_URL,
username=TS_USER,
password=TS_PASSWORD)
reviewer = DataModelReviewer()
issues = reviewer.review_model(test_names=["review_table_joins"],
database=database,
rtql=rtql)
self.assertEqual(2, len(issues['review_table_joins']))
def test_sharding(self):
"""Tests the review of sharding. This test assumes the sharding database has been loaded with data."""
parser = DDLParser(database_name="review_test_sharding")
database = parser.parse_ddl("test_sharding.tql")
rtql = RemoteTQL(hostname=TS_URL,
username=TS_USER,
password=TS_PASSWORD)
reviewer = DataModelReviewer()
issues = reviewer.review_model(database=database, rtql=rtql)
self.assertEqual(6, len(issues["review_sharding"]))
def test_worksheet_join_types(self):
"""Tests the review of the joins between tables in worksheet.."""
parser = DDLParser(database_name="golf_sales")
database = parser.parse_ddl("test_data/golf_sales/golf_sales.tql")
ws_reader = YAMLWorksheetReader()
worksheet = ws_reader.read_from_file(
"test_data/golf_sales/Golf Sales WS.yaml")
rtql = RemoteTQL(hostname=TS_URL,
username=TS_USER,
password=TS_PASSWORD)
reviewer = DataModelReviewer()
issues = reviewer.review_model(database=database,
worksheet=worksheet,
rtql=rtql)
self.assertEqual(3, len(issues["review_worksheet_joins"]))
def main():
"""Runs TQL against a remote cluster."""
args = parse_args()
hostname = args.hostname
try:
rtql = RemoteTQL(hostname=hostname,
username=args.username,
password=args.password)
# This probably only works on Unix systems. TODO add ability to detect Windows and not allow streaming.
i, o, e = select.select([sys.stdin], [], [], 1)
if i:
stream_commands(rtql)
else:
interactive_mode(rtql)
except socket.timeout as t:
eprint(f"Timeout connecting to {hostname}")
except paramiko.ssh_exception.AuthenticationException:
eprint(f"Failed to login as {args.username} on {hostname}")
def setUp(self) -> None:
"""Returns a new TQL shell."""
self.rtql = RemoteTQL(hostname=TEST_HOSTNAME,
username=TEST_USERNAME,
password=TEST_PASSWORD)
class TestRemoteTQL(unittest.TestCase):
"""Test case for testing remote TQL."""
def setUp(self) -> None:
"""Returns a new TQL shell."""
self.rtql = RemoteTQL(hostname=TEST_HOSTNAME,
username=TEST_USERNAME,
password=TEST_PASSWORD)
def tearDown(self) -> None:
"""Returns a new TQL shell."""
del self.rtql
def test_get_databases(self):
databases = self.rtql.get_databases()
# check for a couple of known DBs. Not exhaustive.
self.assertIn("thoughtspot_internal_stats", databases)
self.assertIn("thoughtspot_internal", databases)
def test_get_data(self):
"""Tests making a data query."""
self.rtql.run_tql_command("CREATE DATABASE foo;")
self.rtql.run_tql_command("USE foo;")
self.rtql.run_tql_command(
"CREATE TABLE foo (col1 int, col2 varchar(0));")
self.rtql.run_tql_command("DELETE FROM foo;") # make sure empty.
for row in range(1, 4): # creates three rows 1-3
self.rtql.run_tql_command(
f"INSERT INTO foo VALUES ({row}, 'value_{row}');")
table = self.rtql.execute_tql_query("SELECT * FROM foo;")
self.assertEqual(2, table.nbr_columns())
self.assertEqual(3, table.nbr_rows())
self.rtql.run_tql_command("DROP DATABASE foo;")