Uwe Hesse | about Database Technology

Time 2020-11-04 11:04:15

Web Name: Uwe Hesse | about Database Technology

WebSite: http://uhesse.com

ID:104613

Keywords:

Hesse,Uwe,about,

Description:

With Python integrated to the database, you can utilize the power of SQL together with one of the most popular programming languages. And you run the program where the data is: Inside the database. Instead of having to bring the data to the program.The Exasol database integrates the popular programming languages Python, Java and R as UDF scripting languages, together with the less known but powerful and elegant programming language Lua.--/create or replace python3 scalar script find_books(keyword varchar(2000000)) emits (title varchar(2000000)) asimport urllib.requestimport urllib.parseimport json import sslssl._create_default_https_context = ssl._create_unverified_contextdef run(ctx): with urllib.request.urlopen('https://www.googleapis.com/books/v1/volumes?maxResults=40 q='+ urllib.parse.quote_plus(ctx.keyword)) as url: s = url.read() data = json.loads(s) for item in data["items"]: ctx.emit(item["volumeInfo"]["title"])/We re using the free google api here to access book titles. That script is then called like this:How about sorting that list after length of title? That s something SQL can do very well:select length(title), title from (select find_books('discworld')) order by 1;This should give you an idea about the endless and powerful options you have by combining Python with SQL, both integrated in the database. The TPC-H Benchmark is for Decision Support Systems. It s described very detailed on the TPC.ORG site, but you may find it quite an effort to generate the data and prepare the SQL for table creation and reporting.At least I did, which is why I thought having that all ready for download and run would be helpful.What I have prepared for Oracle and for Exasol is:The data files (CSV format) for the 1 GB TPC-HThe DDL for the TPC-H tablesThe loader commands to populate these tablesThe 22 queries for the TPC-H benchmarkYou can download it here:TPC-H for Oracle          TPC-H for ExasolThe data volume is of course quite small for a production data warehouse but ideal for quick testing and self-education. I m using it together with VirtualBox and VMs on my notebook with 16 GB memory.See here for a demo I m setting up the TPC-H for both Oracle and Exasol and then I do a comparison:Some remarks about the comparison:I m an Exasol employee and the outcome is very positive for Exasol.Never the less, I tried to do a fair comparison. It s just running the pure 22 SELECT statements, no tuning, no tweaking of the Exasol database or the underlying VM.The Oracle version is quite recent (18.3) but not the most recent, same with the Exasol version (6.2), not the just released Exasol 7.0.As you can see, the Exasol database is out of the box about 6 times faster than the Oracle database for the same workload having the same hardware resources without any tuning.I suppose you could get better performance from Oracle for the 22 queries with some effort, like analyzing the workload, adding indexes of the certain available types, partitioning the tables, adding SQL Profiles and Optimizer Directives etc.The point is, that s all not required with Exasol. I just run the workload twice and everything is self-optimized afterwards.You could call this an autonomous database

TAGS:Hesse Uwe about 

<<< Thank you for your visit >>>

about Database Technology

Hot Websites