Database Features
Tips and Tricks on using Exasol - from SQL and Lua Scripts to transactions and performance
Showing results for 
Search instead for 
Did you mean: 
Blog snapshot: This post will: Give you the pros and cons for all the ways you can log code Show you how to use remotelog in Lua with Exasol Walk through how to set it up - and show you how it will take the hassle out of remote coding Until you add logging, scripts running inside of a database are the proverbial black box. You know the input, you know what output you expect and in between is where dark magic happens. Or light magic. Depending on the use case obviously. As much as I like the "Use the source, young Padwan" approach combined with proper unit testing, in complex scenarios there always comes the point where you would like to keep tabs on what's going on inside your scripts. Thus turning the black box a few shades more into the grey. Pun intended — debugging can be very painful at times. What's the best way to log your database scripts?  It really depends on what you're trying to do. Here's a few options for you:  Table logging While logging into tables has the advantage that you have out-of the box search tools for the log, it also means you have to deal with the locking mechanisms (mind the "ck") of the database. Exasol does not support row-locking, so if you start writing messages into a table in parallel — or even write and read in parallel — be prepared to deal with locks and the performance impact that comes with them. Console logging Console logging is useful while unit testing your scripts. Less so when the scripts are running on a remote machine. Depending on the type of script, the console log ends up in the server log files. That is fine for analyzing problems at a later time, but very inconvenient during a debugging session. Especially if log messages from different scripts mix in the same file. Under some circumstances, console logging is not even available. Check the documentation of the script language you are using to see whether or not console logging is an option. Remote logging Remote logging is the perfect choice if you want to watch the log, while your script is running. That is why our Virtual Schemas for example have built-in remote logging. Simply open a log listener on a machine that is reachable from the Exasol instance and enable remote logging. Voilà, you see the log output. It is not ideal for general logging in a production environment though, since the log stream is not fail-safe and the transport is not encrypted. It is a debugging tool and should be used as such. Lua remote logging Since this article series focuses on developing Lua scripts for Exasol, we are going to take a look at remote logging in the Lua world. You will probably not be surprised to learn that we need remote logging for our own scripts too. And we love convenience and simplicity. This is why we wrote remotelog , a Lua module that makes remote logging via a socket connection hassle-free and even allows console logging as an alternative for unit testing. How to access "remotelog" remotelog is a single-file Lua module that only depends on Lua itself and the LuaSocket module. You can either download it from our GitHub repository, or install it from LuaRocks. Since LuaRocks is the established way to get Lua modules, I recommend, you try it out. As an added benefit LuaRocks automatically installs the LuaSocket dependency. Note that this dependency is preinstalled on all recent Exasol versions, so you can use it right out of the box on Exasol. luarocks install remotelog Once you have remotelog on your machine you can use it for unit testing. There are multiple ways to use Lua modules in your Lua scripts (something I'll cover in a later blog). For now let's concentrate on getting to know remotelog . And for that a couple of interactive experiments on your local machine are a good first step. How to work with remotelog Step 1: console logging If you haven't done so yet, please install the Lua interpreter on your machine. To follow this little tutorial, please start the Lua interpreter in interactive mode by typing the following command in a terminal. lua The minimum code you need is shown in the following two lines. The code is written so that you can try it out using Lua's interactive mode. In a script you would declare the log variable local. log = require("remotelog")"Hello world!") Alright, that's pretty unremarkable - but it's unspectacular by design! What this does is give you a log message that looks like this: 2020-09-09 13:36:05 (26143.287ms) [INFO] Hello world! What you see are current date and time and a high resolution time counter, that counts the time since the module was loaded. We'll go through the purpose of this one in a minute. For now, please ignore it. Next we have the log level in square brackets followed by the actual log message. If you now issue the following command, you will notice that no log message appears: log.debug("Wait, why don't I see this?") This is less surprising when you realize the default log level is set to INFO . Let's raise the detail level a bit. log.set_level("DEBUG") log.debug("There you go!") Now we get a log entry again. 2020-09-09 13:45:38 (598749.802ms) [DEBUG] There you go! Step 2: controlling how much you need in your logs Control over the log level is the main feature that separates a logger from plainly print to STDOUT or STDERR . It allows you to control how much detail you need in your logs. For production level INFO is typically find, but when you are debugging, higher detail levels are useful that would otherwise clutter your logs. remotelog offers the following log levels in ascending order of granularity and corresponding functions: NONE - "NONE" probably needs no explanation. FATAL -  fatal()   Use this in cases where everything is lost and immediate termination of the script is the only remaining option. Think of this as the program's last words.   ERROR -  error()   This should be used where there is still a glimpse of hope that the calling code can deal with the problem.  WARN -  warn()  This is for problems that are not an immediate issue, but could turn into an error soon, like low disk space. INFO -  info()   This is the only non-error log level that shows up in a log by default. Use this wisely and scarcely in order to keep logs free of clutter. CONFIG -  config() Use this if you want to provide information about the program's setup or environment. DEBUG - debug() This is your bread-and-butter debug output. It's detailed, but typically on a level that an experienced admin would still be able to make sense of.   TRACE -  trace() This is where all the internal details go that only the selected few initiates into the code can figure out. Memory maps, function traces and alike go here.    Step 3: Timestamps and the high-resolution timer Okay, back to the timestamps and the strange looking timer. Recording date and time down to the second level are a product of Lua's built-in function. If you want to change the format, take a look at the documentation of and check the available format strings. Since you normally set the format before you start logging, you need to use the init() function of remotelog . This function takes two parameters, the timestamp format and a boolean value that decides whether or not the high-resolution timer should be used. The function returns the module reference, so you can directly chain it to the require() function. log = require("remotelog").init("%d.%m.%y", true)"New time format.") yields: 09.09.20 (1977022.523ms) [INFO] New time format. Now let's switch off the high-res timer and see how that looks. log.init("%Y-%d-%m", false)"Another time format.")  As you can see, you can safely call init() again later even if that is seldom required. 2020-09-09 [INFO] Another time format. Using the timer for performance monitoring You can see now that the high-res timer is gone. The reason why it exists is, that Lua does not offer sub-second time resolution. Instead we need another time source to get better precision. While being precise, this time source is not synchronized in any way with the real-time clock. That is the reason why you see milliseconds since the module was loaded instead of the milliseconds fraction of the current time. That means this timer is useful for performance monitoring by looking at differences between timer values. It is less useful as a timestamp though. Remote logging So far so good. But you were being promised remote logging and all we've done so far is console logging. Let's change that, shall we? Step 1 First of all you need a socket log receiver, i.e. a program that can attach listen on a TCP port, wait for incoming connections and then spit out the log message you throw at it. If you are a Linux, BSD or Mac user, you are already set. All modern unix-style environments come with some variant of netcat pre-installed. The Windows fraction on the other hand has to find a suitable application and install that. The team behind Nmap provide a portable version of netcat that works nicely. Open a new terminal and start netcat in listen mode on your machine on port 3000 and tell it to remain open after EOF signals, so that you don't have to reopen it everytime. nc -lkp 3000 Windows users don't have the -k switch though. You can work around this by wrapping netcat in a batch script with a forever-loop. Step 2 - setting up a TCP connection Now we tell remotelog to establish a TCP connection to the listening log receiver. log.connect("localhost", 3000)"Hello netcat!") This is called a reverse connection and has the advantage of better chances in most network setups for the Exasol instance to reach the outside world instead of the other way round. The result of this exercise should now appear on the terminal where netcat is running. 2020-09-09 [INFO] Connected to log receiver listening on localhost:3000 with log level DEBUG. Time zone is UTC+0200. 2020-09-09 [INFO] Hello netcat! What this tells us There are a couple of interesting things to unpack here.  First of all you see a kind of a greeting message that tells you important details about how the subsequent log messages came to exist. They come from a remote machine to the listener. The debug level is displayed and the time zone is given as reference point in case you need to compare the timestamps with logs from other time zones. After that greeting, the log messages look just like on the console. Step 3 You can further improve the greeting by supplying the name of the client (i.e. your script) that is using the logger. I recommend to additionally mention the version number of your script. This helps in the field when you want to figure out, which version of your script was running when a problem surfaced. Let's disconnect, provide a proper client name and reconnect. log.disconnect() log.set_client_name("remotelog-tutorial 1.0.0") log.connect() Now the greeting looks like this: 2020-09-09 [INFO] remotelog-tutorial 1.0.0: Connected to log receiver listening on localhost:3000 with log level DEBUG. Time zone is UTC+0200. That's all the context you need to properly interpret the message after that point. Message formatting You can either concatenate the message strings, or if you want a little bit more convenience, you can use message formatting. remotelog uses the same format strings as Lua's string.format() if you provide two or more parameters in a log function."%s: %d pound sugar, %d pound butter, %d pound flour, %d pound eggs. %3.0f%% awesome.", "Pound cake", 1, 1, 1, 1, 100) And the result of this is: 2020-09-09 [INFO] Pound cake: 1 pound sugar, 1 pound butter, 1 pound flour, 1 pound eggs. 100% awesome. Key points remotelog is a compact Lua module with only a single external dependency. And that dependency is preinstalled on all recent Exasol versions. remotelog provides console and socket logging. You can change the time format, use a high-resolution timer, get a lot context through a greeting message, format message and all of that with little to no effort. What next? In my next blog I will demonstrate how to bundle existing modules directly into a single script using a tool called  amalg
View full article
Blog snapshot In this first part of a series of blogs, you will learn: How to set up Lua Development Tools in Eclipse How to get coding support and what each function does Ways to make testing easier with MoreUnit   Every craft needs the right tools. Imagine a modern master cabinet maker trying to carve the wood with a butter knife - it's unthinkable.   Software development is no different. You need the right tools to code faster, reduce the risk of errors and make your job as easy as possible. But the key is knowing what's right for you - as there are a wide range of IDEs available, all with their own merits and challenges.  The Eclipse IDE is probably best know for its superb Java programming support. And as Eclipse was built around the idea of maximum extensibility, a large ecosystem of plug-ins has grown around it over the years. Which Eclipse plug-ins should you use? Today I want to draw your attention to a collection of Eclipse plug-ins called Lua Development Tools (LTD). A short disclaimer: while the project is stable and works nicely, no features have been added in quite a while, due to the lack of developer availability. How to Setup LTD in Eclipse 20.4 Download Eclipse IDE for Java programmers as a basis (we will learn later with that is a good pick for Lua too) Unpack it in your home directory e.g. under "Applications/Eclipse-LDT" Download the Dynamic Languages Toolkit (DLT) to the temp directory. This isn't part of Eclipse anymore so we need to install it by hand. In Eclipse IDE Help → Install new software... In dialog "Install": Click "Add" In dialog "Add Repository": Click "Archive" Select "/tmp/" Name: "DLTK Core 5.7.1" Click "Add" Select "Uncategorized" in the software list: Click "Next", "Next", accept the license and finish the wizard. Don't restart yet. We have more to install. Help → Install new software... In dialog "Install": Click "Add" In dialog "Add Repository": Name: "Lua Development Tools (LDT)" Location: "" Click "Add" Click "Next", "Next", accept the license and finish the wizard. Again, don't restart just yet. Help → Eclipse Marketplace In dialog "Eclipse Marketplace" Find: "MoreUnit" Click "Install" Click "Confirm" Accept license Click "Finish" The package is unfortunately not signed. So Eclipse wants confirmation at this point that you want to install anyway. Restart. For now we are set. Later we will install another plug-in for even more convenience. Importing a project Let's dive in directly what the LDT can do for you. And to do so, we will use an existing Lua project, Exasol's remotelog. If you worked with a project in a Git repository before, this procedure will look very familiar. Window → Show View → Other... Select the "Git Repositories" view Click "Open" Paste the following link in the empty space in the "Git Repositories" view: You should now have an entry that looks like this: Import the contained project How to get coding support and what each function does You now have a Lua project with implementation, tests and documentation. Next, please open the main implementation file of this project: src/remotelog.lua The first thing you will notice is that you get syntax highlighting for the Lua code and comments and LuaDoc. Of course LDT also brings code completion. Those two things are the minimum feature set you can expect from an IDE. But that's not all. LDT also recognizes LuaDoc comments and displays them nicely. Hover your mouse over the function M.init and you will see, what I mean. Now, scroll all the way down put your cursor on the line reading: start_high_resolution_timer() Click F3 and LDT jumps to the definition of this function. Note that due to the dynamic nature of Lua this works only in simple cases where the LDT can trace back a function reference. If you store the reference in a table for example, LDT does not realize this. Still handy in many situations though. Another useful feature is the "Rename variable" refactoring. Put the cursor on a variable or function and press [ALT] + [SHIFT] +[R]. The name gets selected and if you change it, all related instances get changed too. This is probably the most often used refactoring of all and it is good to have it. Other refactorings like extract method like you know them from Java for example are unfortunately missing. They are on the other hand terribly hard to safely implement on a dynamic language like Lua, so this does not come as a surprise. Another important aspect LDT offers is defining source directories in a project. They are automatically added to the Lua path, enabling you to execute unit tests directly from within Eclipse. Right-click on the Project " remotelog " in the Script Explorer and select "Properties". In the properties dialect find the section Lua/Build path. As you can see I marked the directories "src" and "test" as Lua Sources. While you are here you can also checkout the other project-specific Lua settings LDT offers: Formatter, Grammar and Task Tags. The Formatter is simple, but quite useful if you want to achieve uniformly looking Lua code in a team. In the grammar section you can define which Lua version your code targets. As in other programming languages you can define task tags which you can use as reminders in your comments. They are then displayed in Eclipse's task list. While they are sometimes handy, remember not to check them into version control when you publish your code. Using change tickets is the more professional variant because it keeps your code base clean and allows you to track task progress. One of the most useful functions is that you can run Lua scripts directly from the IDE. Unit testing with LDT This comes in particularly handy in case of unit testing. Open the file " test/test_remotelog.lua ". This file contains a set of unit tests written in LuaUnit. At the end of this file you see a typical LuaUnit idiom: os.exit( This means the file is executable and when you run it, you run the contained unit tests. Hit [CTRL] + [F11] and the unit tests are executed. If you only use [F11] the tests are started in the debugger. To run all unit tests in a project it is customary in the Lua world to provide a script called " run_unit_tests.lua " in the main project directory. You can execute that one too. If you prefer a graphical view of the test results, you can modify the run configuration for " run_unit_tests.lua " to get the test results in a JUnit-compatible XML file. Run → Run Configurations. Find the run configuration for the script " run_unit_tests.lua " Check the "Arguments" tab and notice the following Script arguments -o junit -n ${project_loc}/target/unit_test_report.xml This tells LuaUnit to additionally output the test results in JUnit XML format. And since Eclipse knows that format, you can view it in the JUnit view. Right-click on the generated test report file in the "Script Explorer" and open it with the JUnit view. Testing convenience with MoreUnit MoreUnit is maybe the most useful little extension that I ever stumbled over on the Eclipse Marketplace. With MoreUnit you can switch quickly between implementation and corresponding unit tests using keyboard shortcuts. This is an insanely handy little trick especially if you prefer Test Driven Development (TDD). Let's first install MoreUnit. Help → Eclipse Marketplace... In dialog "Eclipse Marketplace" Search for "MoreUnit" Click "Install" Select all sub-features Click "Confirm" Accept the license Click "Finish" Next we are going to teach MoreUnit how where our test and implementation files reside and how they are named, so that the switch works. Note that for Java you usually can skip that step since there are well-established conventions for naming and location of tests and implementation. In Lua this requires a little extra setup. Window → Preferences Search for "MoreUnit" Click sub-section "User Languages" Enter source and test path, using placeholders for the project directory (here " ${srcProject}/src " and " ${srcProject}/test ") Enter the test file name pattern (in this example " test_${srcFile} " Click "Apply and Close" Now that MoreUnit is configured, open the file " remotelog.lua " in the editor. Press [CTRL] + [J] to jump to the corresponding test file. This works in both directions. When you want to work in TDD, your workflow becomes: ❌ Red Create a test file Create a test Press [CTRL] + [F11] to run it and verify that it fails (which it should since you did not touch the implementation yet!) Press [CTRL] + [J] to jump to the implementation Implement the new functionality ✔️ Green Press [CTRL] +[J] to jump back to the test The test should now succeed 🌟 Clean Press [CTRL] +[J] to jump to the implementation Refactor until you are satisfied with the code quality Press [CTRL] +[J] to jump back to the unit test Rerun the test with [CTRL] +[F11]   Debugging I saved the best part for last. While alt the other features I introduced to you are already very useful, it's debugging where an IDE really sets itself apart from mere editors. Open the file " test/test_remotelog_console.lua ". Double-click the first line in the function test_fatal() to set a break point there. Press [F11] to run the file in the debugger. In the console you can now see the debugger connecting. When you do this the first time Eclipse will offer switching to the Debug perspective. That is a good idea, so confirm this and make it the default. Debugger v1.4.2 Debugger: Trying to connect to ... Debugger: Connection succeed. As expected, the test runs up to the break point. The "Debug" view now shows the stack trace. Additionally you get a variable view, breakpoint view, expressions and an interactive console. In the example below you see how you can inspect the variable hierarchy down from the test's self reference to the log and the VERSION . The breakpoints view contains exactly what you would expect. A list of breakpoint you can enable, disable, remove, import, export and so on. Expressions let you watch the program in realtime and allow you to do conversions. The interactive console is useful if you want to modify the state of the program while your are debugging it. Of course you also have all the stepping options that a regular debugger provides. "Step into", "Step over", "Step return" and so on. I recommend memorizing the keyboard shortcuts. That's more efficient than working with the mouse. Conclusion Lua is a compact but powerful and speedy programming language built right into Exasol to enable high-performance scripting. With the right IDE setup programming and debugging Lua becomes a real pleasure. In the next articles of our "Exasol loves Lua" series we will dive deeper into unit testing and mocking with Lua, remote logging from Lua Scripts and installing standard modules.
View full article
Top Contributors