Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team
Featured Content
-
Optimizing Performance for Qlik Sense Enterprise
This Techspert Talks session addresses: Understanding Back-end Infrastructure Measure Monitor Troubleshoot Environment Qlik Sense Enterprise on Wi... Show MoreThis Techspert Talks session addresses:
- Understanding Back-end Infrastructure
- Measure
- Monitor
- Troubleshoot
Environment
- Qlik Sense Enterprise on Windows
00:00 - Intro
01:22 - Multi-Node Architecture Overview
04:10 - Common Performance Bottlenecks
05:38 - Using iPerf to measure connectivity
09:58 - Performance Monitor Article
10:30 - Setting up Performance Monitor
12:17 - Using Relog to visualize Performance
13:33 - Quick look at Grafana
14:45 - Qlik Scalability Tools
15:23 - Setting up a new scenario
18:26 - Look QSST Analyzer App
19:21 - Optimizing the Repository Service
21:38 - Adjusting the Page File
22:08 - The Sense Admin Playbook
23:10 - Optimizing PostgreSQL
24:29 - Log File Analyzer
27:06 - Summary
27:40 - Q&A: How to evaluate an application?
28:30 - Q&A: How to fix engine performance?
29:25 - Q&A: What about PostgreSQL 9.6 EOL?
30:07 - Q&A: Troubleshooting performance on Azure
31:22 - Q&A: Which nodes consume the most resources?
31:57 - Q&A: How to avoid working set breaches on engine nodes?
34:03 - Q&A: What do QRS log messages mean?
35:45 - Q&A: What about QlikView performance?
36:22 - Closing
Resources:
Qlik Help – Deployment examples
Using Windows Performance Monitor
PostgreSQL Fine Tuning starting point
Qlik Sense Shared Storage – Options and Requirements
Qlik Help – Performance and Scalability
Q&A:
Q: Recently I'm facing Qlik Sense proxy servers RAM overload, although there are 4 nodes and each node it is 16 CPUs and 256G. We have done App optimazation, like delete duplicate app, remove old data, remove unused field...but RAM status still not good, what is next to fix the performace issue? Apply more nodes?
A: Depends on what you mean by “RAM status still not good”. Qlik Data Analytics software will allocate and use memory within the limits established and does not release this memory unless the Low Memory Limit has been reached and cache needs cleaning. If RAM consumption remains high but no other effects, your system is working as expected.
Q: Similar to other database, do you think we need to perform finetuning, cleaning up bad records within PostgresQL , e.g.: once per year?
A: Periodic cleanup, especially in a rapidly changing environment, is certainly recommended. A good starting point: set your Deleted Entity Log table cleanup settings to appropriate values, and avoid clean-up tasks kicking in before user morning rampup.
Q: Does QliKView Server perform similarly to Qlik Sense?
A: It uses the same QIX Engine for data processing. There may be performance differences to the extent that QVW Documents and QVF Apps are completely different concepts.
Q: Is there a simple way (better than restarting QS services)to clean the cache, because chache around 90 % slows down QS?
A: It’s not quite as simple. Qlik Data Analytics software (and by extent, your users) benefits from keeping data cached as long as possible. This way, users consume pre-calculated results from memory instead of computing the same results over and over. Active cache clearing is detrimental to performance. High RAM usage is entirely normal, based Memory Limits defined in QMC. You should not expect Qlik Sense (or QlikView) to manage memory like regular software. If work stops, this does not mean memory consumption will go down, we expect to receive and serve more requests so we keep as much cached as possible. Long winded, but I hope this sets better expectations when considering “bad performance” without the full technical context.
Q: How do we know when CPU hits 100% what the culprit is, for example too many concurrent user loading apps/datasets or mutliple apps qvds reloading? can we see that anywhere?
A: We will provide links to the Log Analysis app I demoed during the webinar, this is a great place to start. Set Repository Performance logs to DEBUG for the QRS performance part, start analysing service resource usage trends and get to know your user patterns.
Q: Can there be repository connectivity issues with too many nodes?
A: You can only grow an environment so far before hitting physical limits to communication. As a best practice, with every new node added, a review of QRS Connection Pools and DB connectivity should be reviewed and increased where necessary. The most usual problem here is: you have added more nodes than connections are allowed to DB or Repository Services. This will almost guarantee communication issues.
Q: Does qlik scalability tools measure browser rendering time as well or just works on API layer?
A: Excellent question, it only evaluates at the API call/response level. For results that include browser-side rendering, other tools are required (LoadRunner, complex to set up, expert help needed).
Transcript:
Hello everyone and welcome to the November edition of Techspert Talks. I’m Troy Raney and I’ll be your host for today's session. Today's presentation is Optimizing Performance for Qlik Sense Enterprise with Mario Petre. Mario why don't you tell us a little bit about yourself?
Hi everyone; good to be here with everybody once again. My name is Mario Petre. I’m a Principal Technical Engineer in the Signature Support Team. I’ve been with Qlik over six years now and since the beginning, I’ve focused on Qlik Sense Enterprise backend services, architecture and performance from the very inception of the product. So, there's a lot of historical knowledge that I want to share with you and hopefully it's an interesting springboard to talk about performance.
Great! Today we're going to be talking about how a Qlik Sense site looks from an architectural perspective; what are things that should be measured when talking about performance; what to monitor after going live; how to troubleshoot and we'll certainly highlight plenty of resources and where to find more details at the end of the session. So Mario, we're talking about performance for Qlik Sense Enterprise on Windows; but ultimately, it's software on a machine.
That's right.
So, first we need to understand what Qlik Sense services are and what type of resources they use. Can you show us an overview from what a multi-node deployment looks like?
Sure. We can take a look at how a large Enterprise environment should be set up.
And I see all the services have been split out onto different nodes. Would you run through the acronyms quickly for us?
Yep. On a consumer node this is where your users come into the Hub. They will come in via the Qlik Proxy Service and consume applications via the Qlik Engine Service, that ultimately connects to the central node and everything else via the Qlik Repository Service.
Okay.
The green box is your front-end services. This is what end users tap into to consume data, but what facilitates that in the background is always the Repository Service.
And what's the difference between the consumer nodes on the top and the bottom?
These two nodes have a Proxy Service that balances against their own engines as well as other engines; while the consumer nodes at the bottom are only there for crunching data.
Okay.
And then we can take a look at the backend side of things. Resources are used to the extent that you're doing reloads, you will have an engine there as well as the primary role for the central node, active and failover which is: the Repository Service to coordinate communication between all the rest of the services. You can also have a separate node for development work. And ultimately we also expect the size of an environment to have a dedicated storage solution and a dedicated central Repository Database host either locally managed or in one of the cloud providers like AWS RDS for example.
Between the front-end and back-end services where's the majority of resource consumption, and what resources do they consume?
Most of the resource allocation here is going to go to the Engine Service; and that will consume CPU and RAM to the extent that it's allocated to the machine. And that is done at the QMC level where you set your Working Set Limits. But in the case of the top nodes, the Proxy Service also has a compute cost as it is managing session connectivity between the end user's browser and the Engine Service on that particular server. And the Repository Service is constantly checking the authorization and permissions. So, ultimately front-end servers make use of both front-end and back-end resources. But you also need to think about connectivity. There is the data streaming from storage to the node where it will be consumed and then loading from that into memory. And these are three different groups of resources: you have compute; you have memory, and you have network connectivity. And all three have to be well suited for the task for this environment to work well.
And we're talking about speed and performance like, how fast is a fast network? How can we even measure that?
So, we would start for any Enterprise environment, we would start at a 10 Gb network speed and ultimately, we expect response time of 4 MS between any node and the storage back end.
Okay. So, what are some common bottlenecks and issues that might arise?
All right. So, let's take a look at some at some examples. The Repository Service failing to communicate with rim nodes, with local services. I would immediately try to verify that the Repository Service connection pool and network connectivity is stable and connect. Let's say apps load very very slow for the first time. This is where network speed really comes into play. Another example: the QMC or the Hub takes a very very long time to load. And for that, we would have to look into the communication between the Repository Service and the Database, because that's where we store all of this metadata that we will try to calculate your permissions based on.
And could that also be related to the rules that people have set up and the number of users accessing?
Absolutely. You can hurt user experience by writing complex rules.
What about lag in the app itself?
This is now being consumed by the Engine Service on the consumer node. So, I would immediately try to evaluate resource consumption on that node, primarily CPU. Another great example for is high Page File usage. We prefer memory for working with applications. So, as soon as we try to cache and pull those results again from disk, performance we'll be suffering. And ultimately, the direct connectivity. How good and stable is the network between the end users machine and the Qlik Sense infrastructure? The symptom will be on the end user side, but the root cause is almost always (I mean 99.9% of the time) will be down to some effect in the environment.
So, to get an understanding of how well the machine works and establish that baseline, what can we use?
One simple way to measure this (CPU, RAM, disk network) is this neat little tool called iPerf.
Okay. And what are we looking at here?
This is my central node.
Okay. And iPerf will measure what exactly?
How fast data transfer is between this central node and a client machine or another server.
And where can people find iPerf?
Great question. iPerf.fr
And it's a free utility, right?
Absolutely.
So, I see you've already got it downloaded there.
Right. You will have to download this package, both on the server and the client machine that you want to test between. We'll run this “As Admin.” We call out the command; we specify that we want it to start in “server mode.” This will be listening for connection attempts.
Okay.
We can define the port. I will use the default one. Those ports can be found in Qlik Help.
Okay.
The format for the output in megabyte; and the interval for refresh 5 seconds is perfectly fine. And then, we want as much output as possible.
Okay.
First, we need to run this. There we go. It started listening. Now, I’m going to switch to my client machine.
So, iPerf is now listening on the server machine and you're moving over to the client machine to run iPerf from there?
Right. Now, we've opened a PowerShell window into iPerf on the client machine. Then we call the iPerf command. This time, we're going to tell it to launch in “Client Mode.” We need to specify an IP address for it to connect to.
And that's the IP address of the server machine?
Right. Again, the port; the format so that every output is exactly the same. And here, we want to update every second.
Okay.
And this is a super cool option: if we use the bytes flag, we can specify the size of the data payload. I’m going to go with a 1 Gb file (1024 Mb). You can also define parallel connections. I want 5 for now.
So, that's like 5 different users or parallel streams of activity of 1 Gb each between the server machine and this client machine?
Right. So, we actually want to measure how fast can we acquire data from the Qlik Sense server onto this client machine. We need to reverse the test. So, we can just run this now and see how fast it performs.
Okay. And did the server machine react the same way?
You can see that it produced output on the listening screen. This is where we started. And then it received and it's displaying its own statistics. And if you want to automate this, so that you have a spot check of throughput capacity between these servers, we need to use the log file option. And then we give it a path. So, I’m gonna say call this “iperf_serverside…” And launch it. And now, no output is produced.
Okay.
So, we can switch back to the client machine.
Okay. So, you're performing the exact same test again, just storing everything in a log file.
The test finished.
Okay. So, that can help you compare between what's being sent to what's being received, and see?
Absolutely. You can definitely have results presented in a way that is easy to compare across machines and across time. And initial results gave us a throughput per file of around 43.6, 46, thereabouts megabytes per second.
So, what about for an end user who's experiencing issues? Can you use iPerf to test the connectivity from a user machine on a different network?
Yep. So, in in the background we will have our server; it's running and waiting for connections. And let's run this connection now from the client machine. We will make sure that the IP address is correct; default port; the output format in megabytes; we want it refreshed every second; and we are transferring 1 Gb; and 5 parallel streams in reverse order. Meaning: we are copying from the server to the client machine. And let's run it.
Just seeing those numbers, they seem to be smaller than what we're seeing from the other machine.
Right. Indeed. I have some stuff in between to force it to talk a little slower. But this is one quick way to identify a spotty connection. This is where a baseline becomes gold; being able to demonstrate that your platform is experiencing a problem. And to quantify and to specify what that problem is going to reduce the time that you spend on outages and make you more effective as an admin.
Okay. That was network. How can admins monitor all the other performance aspects of a deployment? What tools are available and what metrics should they be measuring?
Right. That's a great question. The very basic is just Performance Monitor from Windows.
Okay.
The great thing about that is that we provide templates that also include metrics from our services.
Can you walk us through how to set up the Performance Monitor using one of those templates?Sure thing. So, we're going to switch over first to the central node. So, the first thing that I want to do is create a folder where all of these logs will be stored.
Okay. So, that's a shared folder, good.
And this article is a great place to start. So, we can just download this attachment
So, now it's time to set up a Performance Monitor proper. We need to set up a new Data Collector Set.
Giving it a name.
And create from template. Browse for it, and finish.
Okay. So it’s got the template. That's our new one Qlik Sense Node Monitor, right?
Yep. You'll have multiple servers all writing to the same location. The first thing is to define the name of each individual collector; and you do that here. And you can also provide subdirectory for these connectors, and I suggest to have one per node name. I will call this Central Node.
Everything that comes from this node, yeah.
Correct. You can also select a schedule for when to start these. We have an article on how to make sure that Data Collectors are started when Windows starts. And then a stop condition.
Now, setting up monitors like this; could this actually impact performance negatively?
There is always an overhead to collecting and saving these metrics to a file. But the overhead is negligible.
Okay.
I am happy with how this is defined. Now, this static collector on one of the nodes is already set up. There is an option here that's called Data Manager. What's important here to define is to set a Minimum Free Disk. We could go with 10 Gb, for example; and you can also define a Resource Policy. The important bit is Minimum Free Disk. We want to Delete the Oldest (not the largest) in the Data Collector itself. We should change that directory and make sure that it points to our central location instead of locally; and we'll have to do this for every single node where we set this up.
Okay. So, that's that shared location?
Yep.
And you run the Data Collector there. And it creates a CSV file with all those performance counters. Cool.
So, here we have it now. If we just take a very quick look inside, we'll see a whole bunch of metrics. And if you want to visualize these really really quick, I can show you a quick tip that wasn't on the agenda but since we're here: on Windows, there is a built-in tool called Relog that is specifically designed for reformatting Performance Monitor counters. So, we can use Relog; we'll give it the name of this file; the format will be Binary; the output will be the same, but we'll rename it to BLG; and let's run it.
And now it created a copy in Binary format. Cool thing about this Troy is that: you can just double click on it.
It's already formatted to be a little more readable. Wow! Check that out.
There we go. Another quick tip: since we're here, first thing to do is: select everything and Scale; just to make sure that you're not missing any of the metrics. And this is also a great way to illustrate which service counters and system counters we collect. As you can see, there's quite a few here.
Okay. So, that Performance Monitor is, it's set up; it's running; we can see how it looks; and that is going to run all the time or just when we manually trigger it?
You can definitely configure it to run all the time, and that would be my advice. Its value is really realized as a baseline.
Yeah. Exactly. That was pretty cool seeing how that worked, using all the built-in utilities. And that Relog formatting for the Process Monitor was new to me. Are there any other tools you like to highlight?
Yeah. So, Performance Monitor is built-in. For larger Enterprises that may already be monitoring resources in a centralized way, there's no reason why you shouldn't expect to include the Sense resources into that live monitoring. And this could be done via different solutions out there. A few come to mind like: Grafana, Datadog, Butler SOS, for example from one of our own Qlik luminaries.
Can we take a quick look at Grafana? I’ve heard of that but never seen it.
Sure thing. This is my host monitor sheet. It's nowhere built to a corporate standard, but you can see here I’m looking at resources for the physical host where these VMs are running as well as the domain controller, and the main server where we've been running our CPU tests. And the great part about this is I have historical data as far back I believe as 90 days.
So, this is a cool tool that lets you like take a look at the performance and zoom-in and find the processes that might be causing some peaks or anything you want to investigate?
Right. Exactly. At least come up with a with a narrow time frame for you to look into the other tools and again narrow down the window of your investigation.
Yeah, that could be really helpful. Now I wanted to move on to the Qlik Sense Scalability Tools. Are those available on Qlik community?
That's right. Let me show you where to find them. You can see that we support all current versions including some of the older ones. You will have to go through and download the package and the applications used for analysis afterwards. There is a link over here. So, once the package is downloaded, you will get an installer. And the other cool thing about Scalability Tools is that you can use it to pre-warm the cache on certain applications since Qlik Sense Enterprise doesn't support application pre-loading.
Oh, cool. So, you can throttle up applications into memory like in QlikView. Can we take a look at it?
Yes, absolutely. This is the first thing that you'll see. We'll have to create a new connection. So, I’ll open a simple one that I’ve defined here and we can take a look at what's required just to establish a quick connection to your Qlik Sense site.
Okay, but basically the scenario that you're setting up will simulate activity on a Qlik Sense site to test its performance?
Exactly. You'll need to define your server hostname. This can be any of your proxy nodes in the environment. The virtual proxy prefix. I’ve defined it as Header and authentication method is going to be WebSocket.
Okay.
And then, if we want to look at how virtual users are going to be injected into the system, scroll over here to the user section. Just for this simple test, I’ve set it up for User List where you can define a static list of users like so: User Directory and UserName.
Okay. So, it's going to be taking a look at those 2 users you already predefined and their activity?
Exactly. We need to test the connection to make sure that we can connect to the system. Connection Successful. And then we can proceed with the scenario. This is very simple but let me show you how I got this far. So, the very first thing that we should do is to Open an App.
So, you're dragging away items?
Yep. I’m removing actions from this list. Let's try to change the sheet. A very simple action. And now we have four sheets, and we'll go ahead and select one of them.
Okay, so far, we have Opening the App and immediately changing to a sheet?
Yep. That's right. This will trigger actions in sequence exactly how you define them. It will not take into consideration things like Think Time. I will just define a static weight of 15 seconds, and then you can make selections.
But this is an amazing tool for being able to kind of stress test your system.
It's very very useful and it also provides a huge amount of detail within the results that it produces. One other quick tip: while defining your scenario, use easy to read labels, so that you can identify these in the Results Application. Let's assume that the scenario is defined. We will go ahead and add one last action and that is: to close, to Disconnect the app. We'll call this “OpenApp.” We'll call this “SheetChange.” Make sure you Save. The connection we've tested; we've defined our list of users. First, let's run the scenario. There is one more step to define and that is: to configure an Executor that will use this scenario file to launch a workload against our system. Create a New Sequence.
This is just where all these settings you're defining here are saved?
Correct. This is simply a mapping between the execution job that you're defining and which script scenario should be used. We'll go ahead and grab that. Save it again; and now we can start it. And now in the background if we were to monitor the Qlik Sense environment, we would see some amount of load coming in. We see that we had some kind of issue here: empty ObjectID. Apparently I left something in the script editor; but yeah, you kind of get the idea.
So, all this performance information would then be loaded into an app that is part of the package downloaded from Qlik community. How does that look?
So, here you will see each individual result set, and you can look at multiple-exerciser runs in the single application. Unfortunately, we don't have more than one here to showcase that, but you would see multiple-colored lines. There is metrics for a little bit of everything: your session ramp, your throughput by minute, you can change these.
CPU, RAM. This is great.
Exactly. CPU and RAM. These are these are not connected. We don't have those logs, but you would have them for a setup run on your system. These come from Performance Monitor as well, so you could just use those logs provided that the right template is in place. We see Response Time Distribution by Action, and these are the ones that I’ve asked you to change and name so that they're easy to understand.
Once your deployment is large enough to need to be multi-node and the default settings are no longer the best ones for you, what needs to be adjusted with a Repository Service to keep it from choking or to improve its performance?
That's a great question Troy. So, the first thing that we should take a look at is how the Repository communicates with the backend Database and vice versa. The connection pool for the Repository is always based on core count on the machine. And the best rule of thumb that we have to date is to take your core count on that machine, multiply it by 5, and that will be the max connection pool for the Repository Service for that node.
Can you show us where that connection pool setting can be changed?
Yes. So, we will go ahead and take a look. Here we are on the central node of my environment. You'll have to find your Qlik installation folder. We'll navigate to the Repository folder, Util, QlikSenseUtil, and we'll have to launch this “As Admin.”
Okay.
We'll have to come to the Connection String Editor. Make sure that the path matches. We just have to click on Read so that we get the contents of these files. And the setting that we are about to change is this one.
Okay. So, the maximum number of connections that the Repository can make?
Yes. And this is (again) for each node going towards the Repository Database.
Okay.
Again, this should be a factor of CPU cores multiplied by 5. If 90 is higher than that result, leave 90 in place. Never decrease it.
Okay, that's a good tip.
Right. I change this to 120. I have to Save. What I like to do here is: clear the screen and hit Read again; just to make sure that the changes have been persisted in the file.
Okay.
Once that's done, we can close this. We can restart the environment. We can get out of here.
So, there you adjusted the setting of how many connections this node can make to the QSR. Then assuming we do the same on all nodes, where do we adjust the total number of connections the Repository itself can receive?
That should be a sum of all of the connection strings from all of your nodes plus 110 extra for the central node. By default, here is where you can find that config file: Repository, PostgreSQL, and we'll have to open this one, PostgreSQL. Towards the end of the file…
Just going all the way to the bottom.
Here we have my Max Connections is 300.
Okay. One other setting you mentioned was the Page File and something to be considered. How would we make changes or adjust that setting?
Right. So, this is a Windows level setting that's found in Advanced System Settings; Advanced tab; Performance; and then again Advanced; and here we have Virtual Memory.
Okay.
We have to hit Change. We'll have to leave it at System Managed or understand exactly which values we are choosing and why. If you're not sure, the default should always be System Managed.
Now, I want to know what resources are available for Qlik Sense admins; specifically, what is the Admin Playbook?
It's a great starting place for understanding what duties and responsibilities one should be thinking about when administering a Qlik Sense site.
So, these are a bunch of tools built by Qlik to help analyze your deployment in different ways. I see weekly, monthly, quarterly, yearly, and a lot of different things are available there.
Yeah. So, we can take a look at Task Analysis, for example. The first time you run it, it's going to take about 20 minutes; thereafter about 10. The benefits: it shows you really in depth how to get to the data and then how to tweak the system to work better based on what you have.
Yeah, that's great.
Right? So, not only we put the tools in your hands, but also how to build these tools as you can here. See here, we have instructions on how to come up with these objects from scratch. An absolute must-read for every system admin out there.
Mario, we've talked about optimizing the Qlik Sense Repository Service, but not about Postgres? Do larger Enterprise level deployments affect its performance?
Sure. The thing about Postgres is again: we have to configure it by default for compatibility and not performance. So, it's another component that has to be targeted for optimization.
The detail there that anything over 1 Gb from Postgres might get paged - that sounds like it could certainly impact performance.
Right, because the buffer setting that we have by default is set to 1 Gb; and that means only 1 Gb of physical memory will be allocated to Postgres work. Now, we're talking about the large environment 500 to maybe 5,000 apps. We're talking 1000s of users with about 1000 of them peak concurrency per hour.
So, can we increase that Shared Buffer setting?
Absolutely. And in fact, I want to direct you to a really good article on performance optimization for PostgreSQL. And when we talk about fine-tuning, this article is where I’d like to get started. We talk about certain important factors like the Shared Buffers. So, this is what we define to 1 Gb by default. Their recommendation is to start with 1/4 of physical memory in your system. 1 Gb is definitely not one quarter of the machines out there. So, it needs tweaking.
And again these are settings to be changed on the machine that's hosting the Repository Database, right?
That's correct. That's correct.
Now, is there an app that you're aware of that would be good to kind of look at all these logs and analyze what's going on with the performance?
Absolutely. This is an application that was developed to better understand all of the transactions happening in a particular environment. It reads the log files collected with the Log Collector either via the tool or the QMC itself.
Okay.
It's not built for active monitoring, but rather to enhance troubleshooting.
Sure. So, basically it's good for looking at a short period of time to help troubleshooting?
Right. The Repository itself communicates over APIs between all the nodes and keeps track of all of the activities in the system; and these translate to API calls. If we want to focus on Repository API calls, we can start by looking at transactions.
Okay.
So, this will give us detail about cost. For example, per REST call or API call, we can see which endpoints take the most, duration per user, and this gives you an opportunity to start at a very high level and slowly drill in both in message types and timeframe. Another sheet is the Threads Endpoints and Users; and here you have performance information about how many worker-threads the Repository Service is able to start, what is the Repository CPU consumption, so you can easily identify one. For example, here just by discount, we can see that the preview privileges call for objects is called…
Yeah, a lot.
Over half a million times, right? And represents 73% of the CPU compute cost.
Wow, nice insights.
And then if we look here at the bottom, we can start evaluating time-based patterns and select specific time frames and go into greater detail.
So, I’m assuming this can also show resource consumption as well?
Right. CPU, memory in gigabytes and memory in percent. One neat trick is: to go to the QMC, look at how you've defined your Working Set Limits, and then pre-define reference lines in this chart. So, that it's easier to visualize when those thresholds are close to being reached or breached. And you do that by the add-ons reference lines, and you can define them like this.
That's just to sort of set that to match what's in the QMC?
Exactly.
Makes a powerful visualization. So, you can really map it.
Absolutely. And you can always drill down into specific points in time we can go and check the log details Engine Focus sheet; and this will allow us to browse over time, select things like errors and warnings alone, and then we will have all of the messages that are coming from the log files and what their sources.
Yeah. That's great to have it all kind of collected here in one app, that's great.
Indeed.
To summarize things, we've talked about to understand system performance, a baseline needs to be established. That involves setting up some monitoring. There are lots of options and tools available to do that; and it's really about understanding how the system performs so the measurement and comparisons are possible if things don't perform as expected.
And to begin to optimize as well.
Okay, great. Well now, it's time for Q&A. Please submit your questions through the Q&A panel on the left side of your On24 console. Mario, which question would you like to address first?
We have some great questions already. So, let's see - first one is: how can we evaluate our existing Qlik Sense applications?
This is not something that I’ve covered today, but it's a great question. We have an application on community called App Metadata Analyzer. You can import this into your system and use it to understand the memory footprint of applications and objects within those applications and how they scale inside your system. It will very quickly illustrate if you are shipping applications with extremely large data files (for example) that are almost never used. You can use that as a baseline for both optimizing local applications and also in your efforts to migrating to SaaS, if you feel like you don't want to bother with all of this Performance Monitoring and optimization, you can always choose to use our services and we'll take care of that for you.
Okay, next question.
So, the next question: worker schedulers errors and engine performance. How to fix?
I think I would definitely point you back to this Log Analysis application. Load that time frame where you think something bad happened, and see what kind of insights you can you can get by playing with the data, by exploring the data. And then narrow that search down if you find a specific pattern that seems like the product is misbehaving. Talk to Qlik support. We'll evaluate that with you and determine whether this is a defect or not or if it's just a quirk of how your system is set up. But that Sense Log Analysis app is a great place to start. And going back to the sheet that I showed: Repository and Engine metrics are all collected there. And these come from the performance logs that we already produce from Qlik Sense. You don't need to load any additional performance counters to get those details.
Okay.
All right. So, there is a question here about Postgres 9.6 and the fact that it's soon coming end of life. And I think this is a great moment to talk about this. Qlik Sense client-managed or Qlik Sense Enterprise for Windows supports Postgres 12.5 for new installations since the May release. If you have an existing installation, 9.6 will continue to be used; but there is an article on community on how to in-place upgrade that to 12.5 as a standalone component. So, you don't have to continue using 9.5 if your IT policy is complaining about the fact that it's soon coming to the end of life. As we say, we are aware of this fact; and in fact, we are shipping a new version as of the May 2021 release.
Oh, great.
So, here's an interesting question. If we have Qlik Sense in Azure on a virtual machine, why is the performance so sluggish? How do you fine-tune it? I guess first we need to understand what would you mean by sluggish? But the first thing that I want to point to is: different instance types. So, virtual machines in virtual private cloud providers are optimized for different workloads. And the same is true for AWS, Azure and Google Cloud platform. You will have virtual machines that are optimized for storage; ones that are optimized for compute tasks or application analytics; some that are optimized for memory. Make sure that you've chosen the right instance type and the right level of provisioned iOps for this application. If you feel that your performance is sluggish, start increasing those resources. Go one tier up and reevaluate until you find a an instance type that works for you. If you wish to have these results (let's say beforehand), you will have to consider using the Scalability Tools together with some of your applications against different instance types in Azure to determine which ones work best.
Just to kind of follow up on that question, if we're looking at that multi-node example from Qlik help, what nodes would you consider would require more resources?
Worker nodes in general. And those would be front and back-end.
So, a worker node is something with an engine, right?
Exactly. Something with an engine. It can either be front-facing together with a proxy to serve content, or back-end together with a scheduler a service to perform reload tasks. These will consume all the resources available on a given machine.
Okay.
And this is how the Qlik Sense engine is developed to work. And these resources are almost never released unless there is a reason for it, because us keeping those results cached is what makes the product fast.
Okay.
Oh, here's a great one about avoiding working set breaches on engine nodes. Question says: do you have any tips for avoiding the max memory threshold from the QIX engine? We didn't really cover this this aspect, but as you know the engine allows you to configure memory limits both for the lower and higher memory limit. Understanding how these work; I want to point you back to that QIXs engine white paper. The system will perform certain actions when these thresholds are reached. The first prompt that I have for you in this situation is: understand if these limits are far away from your physical memory limit. By default, Qlik Sense (I believe) uses 70 / 90 as the low and high working sets on a machine. With a lot of RAM, let's say 256 - half a terabyte of RAM, if you leave that low working set limit to 70 percent, that means that by default 30 of your physical RAM will not be used by Qlik Sense. So. always keep in mind that these percentages are based on physical amount of RAM available on the machine, and as soon as you deploy large machines (large: I’m talking 128 Gb and up) you have to redefine these parameters. Raise them up so that you utilize almost all of the resources available on the machine ,and you should be able to visualize that very very easily in the Log Analysis App by going to Engine Load sheet and inserting those reference lines based on where your current working sets are. Of course, the only way really to avoid a working set limit issue is to make sure that you have enough resources. And the system is configured to utilize those resources, so even if you still get them after raising the limit and allowing the - allowing the product to use as much RAM as it can without of course interfering with Windows operations (which is why you should never set these to like 99, 98, 99). Windows needs RAM to operate by itself, and if we let Qlik Sense to take all of it, it will break things. If you've done that and you're still having performance issues, that means you need more resources.
Yeah. It makes sense.
Oh, so here is another interesting question about understanding what certain Qlik Repository Service (QRS) log messages say. There is a question here that says: try to meet the recommendation of network and persistence the network latency should be less than 4 MS, but consistently in our logs we are seeing the QRS security management retrieved privileges in so many milliseconds. Could this be a Repository Service issue or where would you suggest we investigate first? This is an info level message that you are reporting. And it's simply telling you how long it took for the Repository Service to compute the result for that request. That doesn't mean that this is how long it took to talk to the Database and back, or how long it took for the request to reach from client to the server; only how long it took for the Repository Service to look up the metadata look up the security rules and then return a result based on that. And I would say this coming back in 384 milliseconds is rather quick. It depends on how you've defined these security rules. If these security rules are super simple and you are still getting slow responses, we would definitely have to look at resource consumption. But if you want to know how these calls affect resource consumption on the Repository and Postgres side, go back to that Log Analysis App. Raise your Repository performance logs in the QMC to Debug levels so that you get all of the performance information of how long each call took to execute. And try to establish some patterns. See if you have calls that take longer to execute than others; and where are those coming from any specific apps, any specific users? All of these answers come from drilling down into the data via that app that I demoed.
Okay Mario, we have time for one last question.
Right. And I think this is an excellent one to end. We talked a whole bunch here about Qlik Sense, but all of this also applies to QlikView environments. We are always looking at taking a step back and considering all of the resources that are playing in the ecosystem, not just the product itself. And the question asks: is QlikView Server performance similar to how it handles resources Qlik Sense? The answer is: yes. The engine is exactly the same in both products. If you read that white paper, you will understand how it works in both QlikView and Qlik Sense. And the things that you should do to prepare for performance and optimization are exactly the same in both products. Excellent question.
Great. Well, thank you very much Mario!
Oh, it's been my pleasure Troy. That was it for me today. Thank you all for participating. Thank you all for showing up. Thank you Troy for helping me through this very very complicated topic. It's been a blast as always. And to our customers and partners, looking forward to seeing your questions and deeper dives into logs and performance on community.
Okay, great! Thank you everyone! We hope you enjoyed this session. Thank you to Mario for presenting. We appreciate getting experts like Mario to share with us. Here's our legal disclaimer and thank you once again. Have a great rest of your day.
-
Migrating to Qlik Cloud from Client-Managed Qlik Sense or QlikView
Qlik Cloud is a modern analytics and data platform built on the same software engine as QlikView and Qlik Sense Client-Managed and adds significant va... Show MoreQlik Cloud is a modern analytics and data platform built on the same software engine as QlikView and Qlik Sense Client-Managed and adds significant value to empower everyone in an organization to make better decisions daily. Qlik Cloud allows you to use one common platform for all users – executives, decision-makers, and analysts.
Migrating to Qlik Cloud can help your organization:
- Expand your analytics possibilities.
- Reduce infrastructure costs and the total cost of ownership of your Qlik solutions.
- Automate entitlement management and upgrades.
Migrating from Client-Managed Qlik Sense
This site provides you the tools to monitor, manage, and execute a migration from Client-Managed Qlik Sense to Qlik Cloud.
No two client-managed Qlik Sense Enterprise deployments are the same. And no two migrations will be the same. The processes, procedures, and instructions in this section shouldn’t be considered a cookbook. Rather, they’re meant to guide you.
The Qlik Cloud Migration Center provides a general approach to migration along with sequencing, strategy, and best practice recommendations. It also includes tools such as a Qlik Sense app, scripts, and worksheets to aid in planning elements of the migration.
If your organization has a complex deployment with custom tooling, or sophisticated or complicated data integration pipelines, consider contacting your Qlik Customer Support representative.Migrating from QlikView
This site provides comparisons of QlikView and Qlik Cloud, as well as best practices on how to move content, including information about migration assessments and QlikView document conversions.
Recent Documents
-
An error occurred / Failed to load connection error message in Qlik Sense - Serv...
***Changes must be made to all Qlik servers that will not be provided with internet access.***This is mainly due to the Qlik DataMarket reaching out ... Show More
***Changes must be made to all Qlik servers that will not be provided with internet access.***
This is mainly due to the Qlik DataMarket reaching out to the internet although it may not even be used. Undoing or not agreeing to the Qlik DataMarket Terms of Service does not fully disable certain Qlik DataMarket functionalities.NOTE: Qlik DataMarket is being discontinued, and Qlik Sense Enterprise on Windows versions February 2021 and newer will no longer have it included. For updated information see Qlik DataMarket will be retired
For servers not connected to the internet, they may be prompted with a pop-up error when browsing in the Hub or in the Data Load Editor with the following errors:- The Hub has continuous bubbles while loading forever
- The Hub is stock on the initial loading bar
- Failed To Load Connection - Qlik Sense 3.0.x - Feb2018
- Failed To Load Connection - Qlik Sense 2.2
- An Unknown Error Occurred - Qlik Sense 2.1.1 and 2.0.x
Browser Debug tools may display the following error in the console:
Data Market: entry-point-building.js??123145:62
API could not be reached.
Cause:
The Qlik Sense DataMarket connector executable is cryptography signed for authenticity verification, and the .NET Framework's verification procedure when launching an executable includes checking OCSP and Certificate Revocation List information, which means fetching it online if the system doesn't have a fresh cached copy locally.
Qlik DataMarket connector will try to connect Internet to initialize and verify even without using it.
By making the changes in 3.x and June 2017 newer, Qlik Sense will not wait for a response on the verification step, which is the cause of the Failed To Load Connection and related error messages. It will also fix monitoring app issues and problems where the Data Load Editor loads forever. Note: it is highly suggested that all Qlik servers have internet access, however this will help to avoid some issues caused by it.
Resolution:
Option 1 will persist through upgrades, whereas Option 2 would have to be reapplied after every Sense upgrade.
Option 1:
1. Stop all Qlik Sense services on all nodes
2. Edit C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config
3a. If there is an empty <runtime/> tag, change it to<runtime> <generatePublisherEvidence enabled="false"/> </runtime>3b. If there is no <runtime/> tag, add the below anywhere above the closing </configuration> tag.<runtime> <generatePublisherEvidence enabled="false"/> </runtime>
3.c If the <runtime> section has existing content, add generatePublisherEvidence.<runtime> <some other key="value"/> <generatePublisherEvidence enabled="false"/> </runtime>
Example:
4. Save machine.config
5. Repeat on all nodes (if applicable)
6. Start services on all nodesOption 2:
1. Stop all services on all nodes
2. Open C:\Program Files\Common Files\Qlik\Custom Data\QvRestConnector\QvRestConnector.exe.config in an admin level Notepad window
3a. If there is an empty <runtime/> tag, change it to<runtime> <generatePublisherEvidence enabled="false"/> </runtime>3b. If there is no <runtime/> tag, add the below anywhere above the closing </configuration> tag.<runtime> <generatePublisherEvidence enabled="false"/> </runtime>
3c. If the <runtime> section has existing content, add generatePublisherEvidence.<runtime> <some other key="value"/> <generatePublisherEvidence enabled="false"/> </runtime>
4. Repeat on all nodes and repeat for all .config files- C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage\QvOdbcConnectorPackage.exe.config
- C:\Program Files\Common Files\Qlik\Custom Data\QvDataMarketConnector\QvDataMarketConnector.exe.config
- C:\Program Files\Common Files\Qlik\Custom Data\QvSalesforceConnector\QvSalesforceConnector.exe.config
- C:\Program Files\Common Files\Qlik\Custom Data\QvEssbaseConnector
- C:\Program Files\Common Files\Qlik\Custom Data\QvRestConnector
- C:\Program Files\Common Files\Qlik\Custom Data\QvWebStorageProviderConnectorPackage
5. Start all services on all nodes
6. Validate
It may be necessary to include the key immediately before the runtime closing tag if there are many values in the runtime section.
For Qlik Versions 2.x.x [mid-2016 and older]... this will fully disable Qlik Sense DataMarket:
Remove OR rename the file on all nodes
C:\Program Files\Common Files\Qlik\Custom Data\QvDataMarketConnector
NOTE: This folder is likely to be re-created upon upgrade.
-
Upgrading Qlik Sense Repository Database from PostgreSQL 9.6 to 12.5 - New tool ...
This Support article covers functionality available in the new Qlik PostgreSQL Installer (QPI), which allows you to upgrade an existing Qlik Sense Re... Show MoreThis Support article covers functionality available in the new Qlik PostgreSQL Installer (QPI), which allows you to upgrade an existing Qlik Sense Repository Database (QRD) from PostgreSQL 9.6 to PostgreSQL 12.5, or install a brand new QRD based on PostgreSQL 12.5.
PostgreSQL 9.6 has reached End of Life Reference: PostgreSQL Versioning Policy
>> Qlik PostgreSQL Installer - Download Link <<
Environment
- New Qlik Sense Repository Database (QRD) setups for, compatible with Qlik Sense Enterprise Client Managed since November 2019 - Reference: Qlik Sense's compatibility with PostgreSQL for the... - Qlik Community - 1716510
- Existing QRD setups running Qlik Sense embedded PostgreSQL 9.6 (Qlik Sense February 2022 and higher)
Supported Scenarios
- Upgrading an existing QRD from PostgreSQL 9.6 to 12.5. Upgrading earlier versions of QRD is not supported via this method. Instead, upgrade to a version of Qlik Sense that supports 9.6 to automatically migrate through this minor version - List of supported PostgreSQL and early Qlik Sense versions
- Installing a new QRD cluster on a new server, for a new environment or to migrate an existing Database to a separate host.
- Qlik Sense Enterprise on Windows February 2022 and above are supported versions for QPI. Upgrade to a supported version for QPI first and then use QPI to upgrade PostgreSQL.
Considerations
- This installer will not detect a standalone PostgreSQL database installed by any other means, other than through an official Qlik installer package
- The installer itself does not provide a rollback feature, this is described below
- The user who runs the installer has sufficient permissions to execute it and install services
- The user who runs the installer has sufficient permissions to create folders and files in the backup destination
- The backup destination has sufficient free disk space to dump the existing database
- The backup destination is not a network path or virtual storage folder. It is recommended the backup is stored on the main drive
- There will be downtime during this operation, please plan accordingly
Installing a new Qlik Sense Repository Database using PostgreSQL 12.5
- All services on rim nodes must be stopped
- Run the Qlik PostgreSQL Installer
- Click on "Install"
- Accept the Qlik Customer Agreement
- Set your "Local database settings" and click "Next". You will use these details to connect other nodes to the same cluster
- Set your "Database superuser password" and click "Next"
- Review your settings and click "Install", then click "Finish"
- Restore your existing QRD backup OR
- Start installing Qlik Sense Enterprise Client Managed, connecting to an existing cluster
A new service will now be present in your Services management console:
If you are migrating your existing databases to a new host, please remember to reconfigure your nodes to connect to the correct host. How to configure Qlik Sense to use a dedicated Pos... - Qlik Community - 1791775
The tool will display information on the actions being performed. Once installation is finished, you can close the installer.
The PostgreSQL package can be removed via the Control Panel in case you need to uninstall the database at one point.
Upgrading an existing QRD to PostgreSQL 12.5 - Central Node
It is recommended that you first upgrade Qlik Sense to a version that supports QPI, starting with the February 2022 release. More information on Qlik Sense and PostgreSQL compatibility.
Installations other than in C:\Program Files\Qlik\Sense are not supported at the moment.
In this scenario, the new QRD will be using the same port as the old service, and rim nodes do not need any further reconfiguration.
- Stop all services on rim nodes
- On your Central Node, stop all services but Qlik Sense Repository Database
- Run the Qlik PostgreSQL Installer, an existing Database will be detected
- Click on "Upgrade"
- Accept the Qlik Customer Agreement
- Provide your existing "Database superuser password" and click "Next"
- Set your desired backup location and click "Next"
- Click "Upgrade", then exit by clicking "Finish"
- Start all other services on Central Node
- Start all services on all other nodes
Final steps
At this point, it is important to validate that all services and nodes are operating as expected. If it seems like some data is missing from the QMC or elsewhere, you may revert back to the old database instance. To do this, stop the newly installed "postgresql-x64-12" service and set its startup type to Manual. Then start the original "Qlik Sense Repository Database" service, along with all other services.
Once the upgrade and all validation is complete, the old Qlik Sense Repository Database service should be removed on the server.
Perform the following steps to delete the Qlik Sense Repository Database Service
c:\cd ProgramData\Package Cache
c:\ProgramData\Package Cache\dir /s PostgreSQL.msi
The folder containing PostgreSQL.msi will be revealed.
Right click on the PostgreSQL.msi file and select uninstall from the menu.This version is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
If you need to upgrade to a higher release than Qlik provides you can install the same major PostgreSQL release 12.x or up to version 13.
Troubleshooting
If the installation crashes, server reboots unexpectedly during this process or there is a power outage, the new database may not be in a serviceable state. Installation / upgrade logs are available in your temporary files location, for example:
C:\Users\Username\AppData\Local\Temp\2
A backup of the original database contents is available in your chosen location, or by default in:
C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\9.6
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support. The video in this article was recorded in a earlier version of QPI, some screens might differ a little bit.
>> Qlik PostgreSQL Installer - Download Link <<
Related Content
Techspert Talks - Upgrading Qlik Sense Repository Service
Backup and Restore Qlik Sense Enterprise documentation
Migrating Like a Boss
Optimizing Performance for Qlik Sense Enterprise
Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL
How-to reset forgotten PostgreSQL password in Qlik Sense
How to configure Qlik Sense to use a dedicated PostgreSQL database
Troubleshooting Qlik Sense Upgrades -
Transformation: Source Lookup - CLOB datatype - limitations on string functions
Environment Replicate Table/Field level transformation The information in this article is provided as-is and to be used at own discretion. Dependin... Show MoreEnvironment
- Replicate
- Table/Field level transformation
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
Field Transformation
CLOB data types have a limitation in that no string functions can be performed on them in a field transformation. There will be no errors in the log file, just that the field will be NULL.
On my Source table from SQL, I am using the source lookup function under Data Enrichment in order to retrieve the CLOB field value and then apply a string function (replaceChars) to it.
By using the source lookup function on the filed it is returned to the replicate task in a form where a string function can be applied.
This source lookup function is documented in the replicate user guide and the on-line help.
The general form of the function is as follows:
source_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS)
For this example I am doing the lookup into the MyClob table:source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID)
I will then wrap this lookup in a replacechars function:ReplaceChars(source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID),'L','@')
NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
DBOis the schema in oracle
MyCLOB is the table
MyClob is the field that I want returned
MyID=? is the predicate for the lookup
$MyID is the value from the field that we are using in the predicate in place of =?NOTE: That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria.
I will show a series of screen shots below that demonstrate the results of this exercise for the target table:
Source DDL and data for MyClob table:
Target data for MyClob table (NO Transformations):
Target data for MyClob table (ReplaceChars() function on MyString and MyClob fields):
NOTE: There are no errors in the task log - just null values in the field.
MyString field transformation: replaceChars($MyClob,'M','@')
MyCLOB field transformation: replaceChars($MyClob,'L','@')
Target data for MyClob table (ReplaceChars(SourceLookup) function on MyClob fields):
NOTE: You will see that the replaceChars function worked.
MyString field transformation: replaceChars($MyClob,'M','@')
MyCLOB field transformation: ReplaceChars(source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID),'L','@')
Please feel free to try other string functions on the Clob field after once the source lookup returns the value. They should all work.
-
Qlik Sense May 2022: failing to load a field type NCLOB or CLOB when connecting...
The following error or similar could be generated when loading data of type NCLOB or CLOB from an Oracle Data Source. ERROR [HY000] [Qlik][Oracl... Show MoreThe following error or similar could be generated when loading data of type NCLOB or CLOB from an Oracle Data Source.
ERROR [HY000] [Qlik][OracleOCI] (3000) Oracle Caller Interface: ORA-00942:the table or view does not existThe issue was found after upgrading from a previous Qlik Sense version (example QS February 2021 SR4; QS May 2021 SR2) to QS May 2022 SR1 and SR2
Environment
- Qlik Sense Enterprise May 2022
Resolution
- Defect QB-10789 was reported to R&D
- We noticed that only fields of data type NCLOB or CLOB are affected
- A patch with the fix is planned to be delivered with the Qlik Sense August 2022 Patch 1 version
Information provided on this defect is given as is at the time of documenting. For up to date information, please review the most recent Release Notes, or contact support with the ID QB-10789 for reference.
Workaround:
A workaround could be a downgrade of Qlik ODBC connector package to the previous working version. Alternatively you can reach out to Qlik Support in order to get a temporary fixed ODBC connector package version 6.191 (a new official patch will be provided soon).
Cause
Product Defect ID: QB-10789
-
How to troubleshoot Hadoop connectivity issues
In this article, we will be walking through the basic troubleshooting steps required to look into Hadoop endpoint connectivity issues. These steps wi... Show MoreIn this article, we will be walking through the basic troubleshooting steps required to look into Hadoop endpoint connectivity issues.
These steps will focus on investigating two connections:
- HDFS connectivity
- HIVE connectivity
The Scenario
We have implemented two issues by blocking port 50070 and adding the wrong ODBC parameter for the Hive. The outlined troubleshooting steps will highlight how to identify these issues.
In Replicate UI Go to Server Tab --> Logging --> Server Logging Levels and keep the COMMUNICATION and SERVER logging component to Trace during the troubleshooting.
As we begin, we can already see that the connection test fails when we click Test Connection. It fails with SYS-E-HTTPFAIL, Failed to connect to HDFS...
To view the Server logs go to Replicate UI --> Server Tab --> Logging --> Server Logging Levels --> View Logs --> Open the server log to see the below connectivity failed messages:
00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION ]T: requested URL <http://10.51.2.44:50070/webhdfs/v1/?user.name=admin&op=GETFILESTATUS>, action is GET (at_curl_http_client.c:504)
00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION ]T: Clearing (if there is) curl old custom request (at_curl_http_client.c:950)
00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION ]T: calling user pre submit callback (at_curl_http_client.c:1960)
00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION ]T: user pre submit callback returned with status 0 (at_curl_http_client.c:1964)
00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION ]T: preparing response structure; body will be written as dynamic string (at_curl_http_client.c:1428)
00011144: 2022-09-25T00:43:40:395850 [COMMUNICATION ]T: AT_CURL->at_curl_easy_perform: curl_action_status == CURLE_OK failed (at_curl.c:883)
00011144: 2022-09-25T00:43:40:395850 [COMMUNICATION ]T: Failed to curl_easy_perform: curl status = 7, Couldn't connect to server (Failed to connect to 10.51.2.44 port 50070: Bad access) [1000143] (at_curl.c:883)
The error Couldn't connect to server in the logs and Failed to connect in the UI indicates a connectivity issue and we will begin investigating there.
1. Ping test.
This is a quick and common way to verify Hive and HDFS connectivity. It verifies if we have network connectivity at all.
In a command line, we execute:
ping 10.51.2.44
Where 10.51.2.44 is the IP address of the destination server.
Result: PASS
2. Telnet Test
Depending on whether or not we are using Hive or HDFS, the ports used are different. It verifies if the port specified is being listened on.
In a command line, we execute:
telnet 10.51.2.44 50070
Where 10.51.2.44 is the IP address of the destination server.
Result: FAIL
This indicates that port 50070 may not be available (blocked by a firewall, etc).
3. CURL Test
This test is only required for HDFS. We might need to use different authentication mechanisms with CURL based on the deployed authentication mechanisms (like Username\password, Kerberos with key tab, and Kerberos with AD Etc.,).
We must use the exact URL found in the Qlik Replicate log file.
In a command line, we execute:
curl -i -v "HTTP://10.51.2.44:50070/webhdfs/v1/user/admin/USER?user.name=admin&op?GETFILESTATUS"
Where 10.51.2.44 is the IP address of the destination server.
Result: FAIL
Since both the CURL and Telnet tests failed, we look into port 50070 and identify that it has been blocked by a firewall. We enable the port and further tests show Telnet and CURL succeeding.
We can also verify the port using PowerShell and the following command:
Test-NetConnection -ComputerNamer 10.51.2.44 -Port 50070
This is an example of a successful CURL test:
However, when we test the connection in the UI, the connection still fails.
This time it displays a different error:
Cannot enable SSL for the connection when connecting to a server that has not enabled...
To view the Server logs go to Replicate UI --> Server Tab --> Logging --> Server Logging Levels --> View Logs --> Open the server log to see the below connectivity failed messages:
00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION ]T: requested url <http://10.51.2.44:5 0070/webhdfs/v1/?user.name=admin&op=GETFILESTATUS>, action is GET (at_curl_http_client.c:504)
00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION ]T: Clearing (if there is) curl old custom request (at_curl_http_client.c:950)
00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION ]T: calling user pre submit callback (at_curl_http_client.c:1960)
00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION ]T: user pre submit callback returned with status 0 (at_curl_http_client.c:1964)
00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION ]T: preparing response structure; body will be written as dynamic string (at_curl_http_client.c:1428)
00028952: 2022-09-25T01:02:09:912486 [COMMUNICATION ]T: http response code is 200 (at_curl_http_client.c:1186)
00028952: 2022-09-25T01:02:10:127510 [SERVER ]T: going to connect using connection string <ssl=1;HiveServerType=2;Driver={Cloudera ODBC Driver for Apache Hive};HOST=10.51.2.44;PORT=10000;Schema=hive_lz;AuthMech=3;UID=hive;PWD={****};> (hive_odbc_client.c:374)
00028952: 2022-09-25T01:02:10:546553 [INFRASTRUCTURE ]T: AR_ODBC_DRIVER_FUNC->ar_SQLDriverConnect returned -1 (ar_odbc_func.c:1117)
00028952: 2022-09-25T01:02:10:546553 [SERVER ]T: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1160 Message: [Cloudera][DriverSupport] (1160) Cannot enable SSL for the connection when connecting to a server that has not enabled SSL. If the server has SSL enabled, please check if it has been configured to use a SSL protocol version that is lower than what is allowed for the connection. The minimum SSL protocol version allowed for the connection is TLS 1.2. [1022502] (ar_odbc_conn.c:584)
The logs confirm that the HDFS connection worked this time, but not the Hive connection failed.
We verify the Hive connection following the same method as before.
1. Ping test:
In a command line, we execute:
ping 10.51.2.44
Where 10.51.2.44 is the IP address of the destination server.
Result: PASS
2. Port Test
This time we use PowerShell, but you can use telnet as well.
We will use port 10000.
In PowerShell, we execute:
Test-NetConnection -ComputerNamer 10.51.2.44 -Port 10000
Where 10.51.2.44 is the IP address of the destination server.
Result: PASS
3. ODBC Test
We will test the ODBC connection from the Qlik Replicate server:
Result: Pass
4. Review the Hive connection string
Locate the Hive connection string in the Qlik Replicate log and compare it with the DSN setup.
ssl=1; HiveServerType=2;Driver={Cloudera ODBC Driver for Apache Hive};HOST=10.51.2.44;PORT=10000;Schema=swathi_lz;AuthMech=3;UID=hive;PWD={****};
The log indicates that we are using SSL.
But as we review the Hive DSN Setup, we can see that we have SSL unchecked:
Result: Everything matched other than ssl=1.
Let’s review and fix the endpoint settings in Qlik Replicate and perform the test connection by removing the internal parameter of ssl=1.
But what if we receive different error messages?
Generally, driver issues can lead to blockers for Hive connections. If you locate a different error in the UI or the log files, research these errors using either Google or our knowledge base here in the Community, as fixes may already exist.
Environment:
Qlik Replicate
Hadoop Target -
Qlik Replicate LOGSTREAM Timeout while waiting to get data from audit file
In some cases when Replicate can't read from the Staging Folder of a LogStream task for whatever reason (corrupted folder, lack of disk space etc), th... Show MoreIn some cases when Replicate can't read from the Staging Folder of a LogStream task for whatever reason (corrupted folder, lack of disk space etc), there could be difficulties to resume the LogStream task after the initial issue is solved.
You might see errors like the below when trying to resume from timestamp:
[UTILITIES ]E: Failed to write to audit file <audit_folder directory>
[UTILITIES ]E: Timeout while waiting to get data from audit file [1002521] (at_audit_file.c:637)[UTILITIES ]E: Error reading audit batch [1002509] (at_audit_file.c:679)
Environment
- Qlik Replicate 2021.11 (not exclusive)
- Linux RHEL / Windows
Resolution
- Stop all LogStream and Replication tasks
- Kill the replicate sessions and process as the audit file is being locked by the process
- Rename the audit folder of the problematic audit file (the folder is the one described under the endpoint settings: "Staging Folder" in the Replicate UI)
- Resume LogStream task from timestamp (a few hours before the initial error), then resume replication tasks from the same timestamp
- If not solved, a reload of the task will be needed
Cause
Usually, the Replicate process (repctl) is locking the audit file that was being written/read while the issue occurred.
-
Qlik NPrinting: Days to Keep / Reports to Keep Setting Ignored
While running Qlik NPrinting Tasks, many reports are generated. However, the setting for Days to Keep / Reports to Keep seems to be ignored. Environ... Show MoreWhile running Qlik NPrinting Tasks, many reports are generated. However, the setting for Days to Keep / Reports to Keep seems to be ignored.
Environment:
Qlik NPrinting all versions
The first thing to consider is that Qlik NPrinting generates one report for each Newsstand user. For this reason, there will be one file for each user, and not "just one", in the Qlik NPrinting Application Data folder. Those reports may be absolutely identical.
In that sense, the "report to keep" setting does not apply here, and should be considered as "report to keep per user". There is currently an improvement at R&D. Check improvement on days to keep in Nprinting for details. In addition, see Publish task "Reports to Keep" does not work during first 24 hours.
As for the setting apparently being ignored, by default runs the check to delete old reports once every 24 hours. If no new report has been run in the meantime, the old reports will not be deleted, even if more than one day has passed.
Should there be a feeling that the periodic check is not run, the settings can be enforced:- Stop the Qlik NPrinting services Proper Order to Restart Qlik NPrinting Services
- Navigate to and open C:\Program Files\NPrintingServer\NPrinting\Scheduler\scheduler.config
- Add the below lines before the </appSettings> closing tag
<add key="cleanup-period" value="1800" /> <add key="cleanup-period-files" value="43200" /> - Restart the services
The first setting is the recurrence in seconds (default: 30 minutes) for the task that will check for reports in the newsstand and unlink the older ones.
The second setting is the recurrence in seconds (default: 24 hours) for the task that will scan and remove the actual files, after checking that the conditions about days and reports to keep, plus the presence of newer reports that supersede them, are met. In this case, we would change the default value to a shorter time (43200 seconds, 12 hours) to make sure the task is run more frequently. -
QlikSenseUtil as a backup tool
Qlik Sense Util comes bundled with Qlik Sense Enterprise on Windows. It is able to assist in the backing up process to help with the manual process. K... Show MoreQlik Sense Util comes bundled with Qlik Sense Enterprise on Windows. It is able to assist in the backing up process to help with the manual process.
Key elements that need to be backed up are:
- the certificates used by Qlik Sense for internal communication
- the Repository Database used to store meta-data about the Qlik Sense site
- the applications used by the Qlik Sense site
- the Extensions, thumbnail images, etc used by Qlik Sense apps
Other options components can include:
- Logs of activity on the site
- Supplemental material used by the Qlik Sense site (e.g. QVDs, Excel files, etc)
By using the QlikSenseUtil tool, an administrator can back all of these up and configure the tool to be integrated in a silent, incremental backup policy.
How to get started with QlikSenseUtil
- Locate the QlikSenseUtil tool
- Default location: C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
- Custom path example: Drive:\InstallationPath\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
- In Notepad or any text editor of your choice, construct the desired command line syntax for the backup.
- A sample command:
C:\"Program Files"\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe -backup -databaseHostname="FQDNofTheDatabase" -databasePassword="SuperUserPassword" -path="Drive:\BackupLocation" -rootPath="\\ServiceCluster\Path" - For a full listing of the flags possible, consult later in this article or the Readme.txt in the QlikSenseUtil directory.
Note: Executing a backup will stop Qlik Sense services, so only execute this tool when downtime is acceptable
Note: Executing as the Qlik Sense service account is recommended since the client certificate used by Qlik Sense is only accessible to the service account
Note: For silent backups, do remember to use the -f flag
Note: Logs for this tool will be written to %USERPROFILE%\AppData\Local\Temp\logs for the account executing the tool
- A sample command:
- Once the command is written out, launch a command prompt and paste in the command
- Observe the files backed up to the path specified in the path flag
Video Walk-through:
(video from https://www.youtube.com/watch?v=uuF4FMnaD1I?)
Flags:
backup: specifies a backup operation (mandatory)
path: path to the folder where to store the backup (mandatory)
certificatePassword: password for protecting the certificates. If not specified then the certificates will not be backed up
databaseHostname: Hostname where the database is running (mandatory)
databasePassword: Superuser or postgres account password (mandatory, if password was configured)
databasePort: port number used by the database)
f: silent backup
h: helpTo restore your environment, see Backup and restore Qlik Sense Enterprise on Windows.
Related Content:
-
How to allow Anonymous Hub access in Qlik Sense Enterprise on Windows
Three requirements need to be met in order to allow Anonymous users access to the Hub: The product must not be Qlik Sense Business or Qlik Sense Ente... Show MoreThree requirements need to be met in order to allow Anonymous users access to the Hub:
- The product must not be Qlik Sense Business or Qlik Sense Enterprise SaaS.
- The license must support anonymous access, see Which Qlik Sense Enterprise licenses support anonymous access?
- Proxy must be configured to allow Anonymous users
- A Stream dedicated to anonymous users must be created
Useful information:
- Users who access Qlik Sense anonymously are identified as NONE\anonymousd9e61933-a7e5-4836-8540-5b137a600e69, where the GUID is a unique identifier.
- To create a license rule, granting login passes to anonymous users you can use two identifiers:
- userDirectory=NONE
- user.IsAnonymous()
Configure the Proxy to allow Anonymous users
By default, anonymous access is disabled. The steps provided require a restart of the Proxy service after completion.
- Open the Qlik Sense Management Console.
- Choose the Virtual Proxy to be used for anonymous access
- Click Edit.
- In the Properties menu (right side) choose Authentication
- From the Anonymous access mode drop-down select Allow anonymous users
Set up a license rule
Anonymous users will need to have a license rule available for them which gives them either an Analyzer Capacity License or a Login Access Token. The instructions in this article will focus on Analyzter Capacity Licenses.
- Open the Qlik Sense Management Console.
- Navigate to License Management
- Click Analyzer capacity rules
- Click Create new
- Give the new rule a Name (mandatory) and Description (optional)
- Create one of two possible rules:
- In the Basic settings, select
user: userDirectory =
value: NONE - In the Advanced settings (see Fig 2), provide the condition:
user.IsAnonymous()Fig 2
- In the Basic settings, select
- Click Apply
Set up a Stream for anonymous access
You can use the default Everyone stream, which has already been set up for anonymous access, or create your own.
We will create two rules. One to allow logged in (authenticated) users access and publishing permissions. One to allow anonymous user access, but no publishing permissons. Note that based on your requirements, this may need to modified.
- Go to the Qlik Sense Management Console
- Go to Manage Content and Streams
- Click Create New
- Name your new Stream
- Click Security Rules
- Click Create associated rule (see Fig 2)
This will be our "Everyone (anonymous) can read the stream." rule.Fig 3
- Provide a Name (mandator) and Description (optional)
- In Basic only select Read
- In Advanced define
Condition: user.IsAnonymous()
Context: Only in hub
See Fig 4.Fig 4
- Click Apply
- Click Create associated rule (see Fig 2)
This will be our "Everyone (logged in) can access this stream and publish." rule. - Provide a Name (mandator) and Description (optional)
- In Basic only select Read and Publish.
- In Advanced define
Condition: !user.IsAnonymous()
Context: Both in hub and QMC - Click Apply
Test!
We are now ready to test access.
- Go to the Qlik Sense Hub (hosted by the Virtual Proxy you have configured)
- If you are logged in, log out.
- Our new Anonymous stream is now avaiable.
-
Using Qlik Application Automation to create and distribute Excel reports in Offi...
Getting data out of Qlik Sense Enterprise SaaS, and distributing it to different users in formatted Excel, has been a manual task until now. Thanks to... Show MoreGetting data out of Qlik Sense Enterprise SaaS, and distributing it to different users in formatted Excel, has been a manual task until now.
Thanks to the release of Qlik Application Automation, it is now possible to automate this workflow by leveraging the connectors for Office 365 - specifically Microsoft SharePoint and Microsoft Excel.
Here I share two example QAA workspaces that you can use and modify to suit your requirements.
Video:
Considerations
- QAA cannot currently attach files using the Mail block, instead this approach distributes a SharePoint link.
- QAA has a limit of 100,000 rows when getting data out of a Qlik Sense straight table object.
- The On-Demand example uses an extension in QSE SaaS to send data to the Automation. An update to the Qlik Sense Button object is expected soon, which will provide a native way to pass selections to an Automation.
Example 1 - Scheduled Reports
- Download the 'Scheduled Report.json' file attached to this document.
- Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right clicking in the editor canvas, and selecting 'Upload workspace'.
- Select the 'Create Binary File (Personal One Drive)' block, select 'Connection' in the block configurator to the right, and then create your connection to Microsoft SharePoint.
- Select the 'Get Straight Table Data' block. Under 'Inputs' in the block configurator, lookup your the App Id, Sheet Id, and Object Id for the relevant QSE SaaS table you wish to output.
- Select the 'Create Excel Table With Headers' block, select 'Connection' in the block configurator, and then create your connection to Microsoft Excel.
- Select the 'Send Mail' block. Under 'Inputs' in the block configurator update the 'To' to reflect the addresses you wish to deliver to.
- With the 'Send Mail' block still selected, select 'Connection' in the block configurator and add your Sender details.
- To test, Save and then Run the Automation
- If you receive any warnings or errors, navigate to the relevant blocks and ensure your Connection is selected in the block configurator.
- Select the 'Start' block. Under 'Inputs' in the block configurator, change Run Mode to Scheduled and define your required schedule.
Example 2 - On-Demand Reports
Note - These instructions assume you have already created connections as required in Example 1.
- Download the 'On-Demand Report v3.json' file attached to this document.
- Download and install the 'qlik-blends' extension. See:
https://github.com/rileymd88/qlik-blends/files/6378232/qlik-blends.zip - Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right clicking in the editor canvas, and selecting 'Upload workspace'.
- Ensure your Connections are selected in the block configurator for each of the following blocks, 'Create Binary File (Personal One Drive)', 'Create Excel Table With Headers', 'Add Rows To Excel Worksheet Table (Batch)', 'Create Sharing Link', and 'Send Mail'. Save the Automation.
- Select the 'Start' block and ensure Run Mode is set to Triggered. Make note of the of URL and Execution Token shown in the POST example.
- Open your chosen QSE SaaS application, and Edit the Sheet where you wish to add a Button to trigger an On-Demand report.
- Under 'Custom Objects' look for 'qlik-blends' from the Extensions menu and drag this into your Sheet.
- Under the 'Blend' properties to the right, add-in your POST webhook URL and Token as noted in Step 5.
- We will now add three measures to the 'qlik-blends' object. It is important you add them in the order described. Add the first measure, using the following function in the expression editor: GetCurrentSelections()
- Add the second measure, using the following function DocumentName()
- The final measure will be the Object ID of the table you wish to use. To find the Object ID, select 'Done Editing'. Then right click on the table, select 'Share', select 'Embed', then look for the Object ID under the preview. Copy this value, go back into Editing mode and paste this as your third measure value.
- With the 'qlik-blends' object selected, under Form select 'Add items'. For 'Item type' select Text. Under default value you can choose to add a default email address. For 'Label' and 'Reference' type 'Email'. It is critical that Reference is updated to 'Email'. Turn 'Required input' on.
- You can change the Appearance properties to suit your preferences, such as updating the Button label and message, enabling Dialog, and changing the Color under Theme.
- Back in the Automation, under the Start Block. Set 'Run asynchronously' = yes to allow multiple requests to run at the same time (This will also increase the max run time from 1min to 60min)
- Once happy, test the On-Demand report by entering an email and clicking the button.
This On-Demand Report Automation can be used across multiple apps and tables. Simply copy the extension object between apps & sheets, and update the Object ID (Measure 3) for each instance.
Environment
- Qlik Application Automation
- Qlik Cloud
- Microsoft Office 365
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
-
License and operation monitoring apps error 401 Unauthorized
Monitoring App reload fails with:Error message: Error: QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 401 (Unauthorized): Requested resource requires... Show MoreMonitoring App reload fails with:
Error message: Error: QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 401 (Unauthorized): Requested resource requires authentication.
The error happens when the Qlik Sense services account does not match the account used in the reload task.Resolution:
The data connections must be updated accordingly with the Qlik Service User ID and Password.- Open the Qlik Sense Management Console
- Navigate to Data Connections
- Locate monitor_apps_REST_ data connection
- Click Edit
- Enter the correct User ID and Password (as seen in Fig 1)
- Save the changes.
Fig 1
-
How to: Getting started with the Splunk connector in Qlik Application Automation
This article gives an overview of the Splunk Connector in Qlik Application Automation.The Splunk connector has been created with the following use cas... Show MoreThis article gives an overview of the Splunk Connector in Qlik Application Automation.
The Splunk connector has been created with the following use cases in mind:- Being able to perform searches to a Splunk instance
- Inserting new events into Splunk using the HTTP Event Collector
Connecting the Splunk connector
The Splunk connector makes use of basic authentication. When connecting the connector, you will be prompted with the following popup screen:
Hostname: The hostname or IP address of the Splunk instance. Exclude the http(s):// part and the port number of the URL.
Username: The username you use to login to Splunk
Password: The password you use to login to Splunk
Use SSL: Select whether the management service of Splunk makes use of SSL. Default yes.
Management port: The port on which the management service of Splunk is running. Default is 8089. Providing no value will omit the port from the final URL.Performing a search on Splunk
To perform searches using the Splunk connector it is possible to perform a synchronous search as well as an asynchronous search. The synchronous search has a timeout of 50 seconds, with the asynchronous search it is possible to have a longer window before the automation will timeout.
Synchronous Search
The synchronous search can be done using the Synchronous Search block. In the Search Query block you will have to provide a splunk search query.
Asynchronous Search
The asynchronous search block can be manually done using the block Asynchronous Search block. Then perform polling using the Get Search block using the returned Search ID. When finished, results can be obtained using the Get Results From Search block.
Alternatively it is possible to use the block Do Asynchronous Search and get Results. This block will perform the polling, only a search query has to be provided to obtain a list of results.
Inserting data into Splunk
The other use of the Splunk connector is to insert data into Splunk by making use of the HTTP Event Collector. An example usecase for this would be to send reload logs of a Qlik Sense app into Splunk.
To do this we will first have to create an HTTP Event Collector on the Splunk side.To do this in Splunk go to Splunk in the web browser and go to Settings > Data > Data Inputs.
On this page you will see Http Event Collector. Press the Add new button.Provide a name for the token and go to the next page. Select indexes to which this token can write. Click on Review and Submit. You will be given a token that you need to configure on the block.
In Qlik Application Automation make use of the Insert Data block. For the inputs, enter the HEC token value. Provide the port for the HTTP event collector. This can be obtained from the Splunk HTTP Event Collector page when clicking on the Global Settings button. You can also find whether SSL is enabled for the HTTP Event Collector, select this value for the Hec_Use_Ssl parameter. Next provide the index to which you're writing your events and provide a source string.
For data, you can send a JSON message. Event is a mandatory key to write in this JSON message.The following example will perform a reload and send the reload log to Splunk:
In Splunk it is then possible to view the logs of the reload:
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
-
Qlik SaaS: adding image <URL> to a table as column, by default some image <URLS> do not re</URLS></URL>...
In Qlik Saas, when adding an image <URL> https://play-lh.googleusercontent.com/ahJtMe0vfOlAu1XJVQ6rcaGrQBgtrEZQefHy7SXB7jpijKhu1Kkox90XDuH8RmcBOXNn a... Show MoreIn Qlik Saas, when adding an image <URL> https://play-lh.googleusercontent.com/ahJtMe0vfOlAu1XJVQ6rcaGrQBgtrEZQefHy7SXB7jpijKhu1Kkox90XDuH8RmcBOXNn as a column to a table, we expected this to fail because it should require "img-src" directive to be checked in "Content Security Policy".
However, the logo with image <url> above is displayed correctly (see logo dispayed as Dim1 column in below screenshot ).
Adding a new column 'Alpha' with image <url> https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTaANtept27EFdcIQ62k2W2VCkpXSq0iDxT4w&usqp=CAU does not display the expected image.
Environment
- Qlik Sense SaaS
Resolution
From a close look to console tab (of dev-tool), we noticed that some image <urls> are recognized by default (see above attached screenshot). They do not require the use of directive "img-src ".
Refused to load the image '<URL>' because it violates the following Content Security Policy directive: "img-src 'self' data: maps.qlikcloud.com ibasemaps-api.arcgis.com cdn.pendo.io app.pendo.io pendo-static-5763789454311424.storage.googleapis.com data.pendo.io *.gravatar.com *.wp.com *.googleusercontent.com cdn.qlik-stage.com cdn.qlikcloud.com upload.wikimedia.org".A documentation request HLP-13324 was submitted to R&D .
Cause
Product Defect ID: HLP-13324
-
How to setup Key Pair Authentication in Snowflake and How to configure this enha...
Snowflake supports using key pair authentication for enhanced authentication security as an alternative to basic authentication (i.e. username and pas... Show MoreSnowflake supports using key pair authentication for enhanced authentication security as an alternative to basic authentication (i.e. username and password). This article covers end-to-end setup for Key Pair Authentication in Snowflake and Qlik Replicate.
This authentication method requires, at minimum, a 2048-bit RSA key pair. You can generate the Privacy Enhanced Mail (i.e. PEM) private-public key pair using OpenSSL.
Qlik Replicate will use the ODBC driver to connect snowflake and ODBC is one of the supported clients which will support key pair authentication.
Let's assume, you decided to use key pair authentication for the Snowflake user which is used in Qlik Replicate to connect to Snowflake. You have to follow the below process to convert user authentication from basic to key pair.
Step 1: Generate the Private Key
You can generate either an encrypted version of the private key or an unencrypted version of the private key.
To generate an unencrypted version use the following command in the command prompt:
$ openssl genrsa 2048|openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
To generate an encrypted version (which omits -nocrypt) use:
$ openssl genrsa 2048|openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
In our example case, we generate an encrypted version of a private key.
We:
- Open a command prompt
- Run $ openssl genrsa 2048|openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
- Enter our password
- And store the password to use in Qlik Replicate.
This generates a private key in PEM format:
Step 2: Generate a Public Key
From the command line, we generate the public key by referencing the private key. The following command assumes the private key is encrypted and contained in the file named rsa_key.p8.
When it requests a passphrase, use the same password that we generated in step 1.
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
This command generates the public key in PEM format:
Step 3: Store the Private and Public Keys Securely
Copy the public and private key files to a local directory for storage and record the path to the files. Note that the private key is stored using the PKCS#8 (Public Key Cryptography Standards) format and is encrypted using the passphrase you specified in the previous step.
However, the file should still be protected from unauthorized access using the file permission mechanism provided by your operating system. It is your responsibility to secure the file when it is not being used.
Step 4: Assign the Public Key to a Snowflake User
Describe the user to see current information. We can see that there is no public key assigned to the HDW user. Therefore, the user needs to use basic authentication.
Execute an ALTER USER command to assign the public key to a Snowflake user.
Step 5: Verify the User’s Public Key Fingerprint
Execute a DESCRIBE USER command to verify the user’s public key.
Step 6: Configure Qlik Replicate to Use snowflake Key Pair Authentication
- Enter the snowflake server name and the username that we configured key pair authentication.
- We need to set the below parameters to set the authentication method as SNOWFLAKE_JWT, passing the private key and password from step 1.
- Try to do the test connection\browse the database and you can see a missing password error. We can't ignore the password in the endpoint UI and at the same time, we shouldn't enter the password as we are using keypair authentication.
- You can key in a dummy password, such as dummy, dummy123, etc., in the UI to eliminate the missing password error.
Finally, browse the database and do the test connection.
Environment
Qlik Replicate
Snowflake Target -
Changes applied to QlikView Client Managed license assignment do not reflect in ...
When buying a QlikView Client Managed license with SaaS add-on together with a Qlik Sense Enterprise license for the cloud, changes applied to one of ... Show MoreWhen buying a QlikView Client Managed license with SaaS add-on together with a Qlik Sense Enterprise license for the cloud, changes applied to one of the environment are not reflected in the other ones.
So, it is not possible to understand, from one single window, how many Professional and Analyzer seats have been assigned in total. Customers need therefore to check both environments and count the assigned license manually.
Environment
- Qlik Cloud
- QlikView, any
Resolution
As of September 2022, the QlikView with-SaaS-add-on and the Qlik Cloud licenses are tied only by contractual agreement.
There is no technical integration between the two.
It's the customer's contractual responsibility to make sure that the number of allocated professional and analyser licenses are not exceeded. As of now, there is no technical functionality, for the customer, at the moment, to cross-verify the two serial numbers or to block allocating more seats than what was agreed contractually. -
Qlik AutoML: How are confusion matrix values generated?
Introduction: For binary classification models, AutoML will generate a confusion matrix after running a model experiment. This represents the True P... Show MoreIntroduction:
For binary classification models, AutoML will generate a confusion matrix after running a model experiment. This represents the True Positives, True Negatives, False Positives (model predicted true but target value was false), and False Negatives (model predicted false but target value was true). Classification explanation here.
Explanation:
The confusion matrix is calculated from the hold out set for data training.
During the model training phase, the tool automatically splits data between training and hold out sets on a 80/20 basis.
The training data is split through a k fold iteration and trained on multiple samples. The hold out dataset is held to the end of this process and then the model is run against this to generate the model metrics and confusion matrix. You can think of the hold out as showing how the model performs against something it has never seen before.Environment
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
-
Qlik Downloads page shows "401 Unauthorized Access" error
Qlik Download page in Community does not show the expected product and download page. The web page and browser console display errors messages referr... Show MoreQlik Download page in Community does not show the expected product and download page.
The web page and browser console display errors messages referring to HTTP 401 Unauthorized access, which may look similar to below examples.
- {"errors":[{"title":"State verification failed","detail":"State not valid, missing request forgery protection","code":"STATE-1","status":"401"}],"traceId":"{ID}"}
- VM624:1 GET https://download.us.qlikcloud.com/api/v1/users/me 401
- VM624:1 GET https://download.us.qlikcloud.com/api/v1/csrf-token?qlik-web-integration-id={ID} 401
Environment
- Qlik Download page; https://community.qlik.com/t5/Downloads/tkb-p/Downloads
Resolution
Qlik Download page in Qlik Community requires 3rd party cookies as part of the current web integration. The accessing browser must allow must allow 3rd party cookies while accessing the Qlik Downloads page, in order for the page to render successfully.
The browser does not have to completely allow 3rd party cookies, but can also just allow 3rd cookies for the *.qlik.com or community.qlik.com domain.
The cookie settings are browser-specific, please consult browser help for more details.
Cause
Qlik Download page in Qlik Community is composed by an embedded object that is hosted in Qlik Cloud. This means that cookies for the user session are associated with two different domains, community.qlik.com and qlikcloud.com. The browser refer to the parent page (community.qlik.com) as a 1st party cookie, while the enmbedded content from a different domain (qlikcloud.com) is referred to as a 3rd party cookie.
3rd party cookies may be blocked in a browser as a mechanisms to block user tracking and advertisment. Browser incogniro mode may also block 3rd party cookies by default as part of keeping the user more anonmyous.
Related Content
- Google Chrome: https://support.google.com/chrome/answer/95647?hl=en&co=GENIE.Platform%3DDesktop
- Firefox: https://support.mozilla.org/en-US/kb/third-party-cookies-firefox-tracking-protection
- Microsoft Edge: https://support.microsoft.com/en-us/windows/microsoft-edge-browsing-data-and-privacy-bb8174ba-9d73-dcf2-9b4a-c582b4e640dd
- Apple Safari: https://support.apple.com/en-au/guide/safari/sfri11471/mac
-
SAP Extractor Connector uses the default formatting in the Thousand and Decimal...
In Qlik Sense, the thousands and decimal separator must be set as comma and point by default (as shown below), while using the SAP extractor connecto... Show MoreIn Qlik Sense, the thousands and decimal separator must be set as comma and point by default (as shown below), while using the SAP extractor connector, to avoid a non-automatic conversion of values, when a field for instance is a CHAR (255) that contains floating point numbers.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';Environment:
Qlik Sense Enterprise on Windows
Qlik SAP ConnectorsInternal Investigation ID(s):
QB-14332
-
Qlik Replicate Troubleshooting
This Techspert Talks session addresses the following:- How Qlik Replicate Works- Understanding Log Files- Quickly Resolving Issues 00:00 - Intro 0... Show MoreThis Techspert Talks session addresses the following:
- How Qlik Replicate Works
- Understanding Log Files
- Quickly Resolving Issues00:00 - Intro
01:02 - What Qlik Replicate does
01:41 - How to create a task
05:02 - Troubleshooting Full Load error
06:06 - Analyzing the task logs
08:54 - Setting the correct schema in task settings
11:49 - Troubleshooting Change Data Capture (CDC)
13:38 - Solution in Mainframe
15:10 - Solution in Replicate
16:34 - Testing Data Capture Changes
18:03 - Q&A: How to do Replication without Primary Keys?
19:05 - Q&A: How to fix missing records?
19:58 - Q&A: What are best practices for Full Load and CDC?
21:03 - Q&A: Where is help for installing Replicate?
21:46 - Q&A: What about communication error?
22:49 - Q&A: Can Replicate have multiple tasks with same Source?
23:31 - Q&A: Where to find driver version info?
24:43 - Q&A: How to know the build number?
25:43 - Q&A: What is the N column in SQL error message?
26:38 - Q&A: Where is Kafka endpoint documentation?
27:28 - Q&A: How to find the table ID referenced in the logs?
28:16 - Q&A: What is restarting with Advanced Run Options?
28:50 - Q&A: What is Resume Processing?
29:12 - Q&A: Can you have multiple tasks pointing to the same target?
29:31 - Q&A: What log files are there?
30:21 - Q&A: Where to turn on Verbose logging?
31:19 - Q&A: How to get error-triggered verbose logging?
32:36 - Troubleshooting Recommendations
Resources:
Qlik Replicate User Guide (Help.Qlik.com)
Installing Qlik Replicate documentation
Troubleshooting CDC Missing Data
Troubleshooting Missing Data Durring Full Load
Adding and managing End Points - documentation
Q&A:
Q: Is it possible to convert to consumable data?
A: Further research is needed. Can you open a case and give an example so that we may thoroughly answer your question?
Q: Sometime log files will be missing? Have you ever had that kind of issue? Did customers ever report that?
A: Yes, there can be multiple reasons for missing database logs. Sometimes it is due to a situation where failovers occur and archive logs are not available on the other node, or in DB2 LUW they can become unavailable if not managed properly by the log management facility for archiving and de-archiving, in a timely fashion. Sometimes there have been IBM DB2 Patched needed to be applied. Definitely search the Community and if there are no solutions there then open a case and attach a Diagnostic Package. This article should be helpful: What information to provide when troubleshooting missing data?
Q: I don’t see the option to automatically enable data capture change from the Replicate side. Is that option only in db2 source endpoint?
A: Yes, this is fore DB2 Z/OS and DB2 LUW endpoint. N. Not for the DB2 iSeries endpoint. It is not in other Sources like Oracle or SQL Server.
Q: Hi, is there is an option to find out if Replicate was able to replicate a specific record based on specific key? Do we need to enable control tables for it?
A: The Source_Capture set to Verbose may help as well as other Components may need Verbose Logging. It really depends on the Source type. You can try setting DATA_RECORD to verbose. Unfortunately, it does not allow you to select by Key, but you turn on the Store Changes (in Task Settings) to help expose the data records and their keys and find it in the target for a table called “your schema.tablename__CT”
Q: I have a specific problem when I activate the CDC, it shows the error.
"Cannot initialize subtask. Failed while preparing stream component 'st_0_SqlServer2019-111'. Please add TRACE logging to the component that is showing that message.
RetCode: SQL_ERROR SqlState: 42000 NativeError: 20028 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The Distributor has not been installed correctly. Could not enable database for publishing. Line: 1 Column: -1 Failed (retcode -1) to execute statement: 'exec sp_replicationdboption @dbname = N'Northwind', @optname = N'publish', @value = N'true''
A: Yes this message is a SQL Server Message. I would first check with the SQL Server DBA on what the message means and search the Community for what other customers did to resolve the issue. Also, you can add TRACE logging to the component that is showing that message. Rerun the task and if it does not show enough information then open a case and attach the Diagnostic Package.
Q: I had a "snapshot too big" issue on one of my task when using Oracle as a source. Is replicate doing a snapshot of the table to help it keep track of the changes happening on the table during the full load ?
A: Yes, search the community for that error message. See if these article might be helpful: Loading a big table in Qlik Replicate or ORA-12899: value too large for column
Q: I am seeing now and then some records get added into apply_expection table, how to debug the reason - any specific log level to be increased from Info.
A: YES, you can change the Target_apply to trace or verbose as needed BUT ONLY keep the extra logging on for a FEW MINUTES ONLY and then set it back to INFO when done. Then you can examine the contents of the attrep_apply_changes as it will have the INSERT/UPDATE/DELETE and the associated erro message. Depending on the error search the Community and ask your DBA, then if you cannot resolve it then open a case and attach the DIAGNOSTIC PACKAGE to the case.
Q: Is the troubleshooting process similar in cases where Logstream is used for CDC?
A: Yes, it is the exact same. Great question! View the logs search for the problem, add TRACE or VERBOSE logging only for a few minutes during the problem, then put the logging back to INFO. Search the community for the messages. If you cannot find the resolution and if it is a SQL error you can either Google it and discuss it with your DBA, or if needed, open the case and attach the Diagnostic Package to the case.
Q: Which material should we go through in order to prepare for the qlik replicate certification?
A: Review the User Guide extensively and peruse through the Community for How To’s. There are also some excellent courses on Learning.Qlik.com for ramping up on everything to do with Qlik Replicate
Q: When using DB2z as a source and Kafka as a target - we see that empty fields (not NULL fields - actual empty fields filled with blanks) at the source are not sent at all in the data message. Is there an option to force Replicate to send empty fields? We would like to be able to differentiate empty fields from NULL fields when reading the attunity messages in Kafka.
A: Yes, if you have DB2 z/OS going to Kafka, you should select the row of the table and see the row in HEX ( HEX ON ) . Do you see 40’s which are BLANKS(SPACES) in the field? If so, they can come through to the target. We would need your Diag Pkg, and CREATE TABLE DDL to reproduce it in-house. A transformation may or may not be needed. Please create a new case.
Q: Tasks can be exported in JSON, and then you can compare versions exported on a regular basis.
A: Yes, you can compare the versions of Replicate in the exported task. It will look like this:
"version": "2022.5.0.499"Q: Barb, In your demo you mentioned you can re-run Full Load to grab the 8 records that you missed, if you do that does not duplicate records on your MS SQL Databse?
A: The Duplicate records can be easily handled by going in the task settings, then Error Handling, then Apply Conflicts. Change the following. “Change to task policy” . Then Duplicate key when applying INSERT: should become “UPDATE the existing target record” and No record found for applying an UPDATE: should become “INSERT the missing target record” Save the settings and reload the table.
-
Qlik Cloud with ADFS as IDP: verify if refresh tokens are being used
If using ADFS as an IDP (Identity Provider) in order to conform to Qlik's security requirements, ADFS must be configured to use refresh tokens and not... Show MoreIf using ADFS as an IDP (Identity Provider) in order to conform to Qlik's security requirements, ADFS must be configured to use refresh tokens and not access tokens. If refresh tokens are not configured this configuration will not be supported.
To confirm if ADFS is configured correctly to allow refresh tokens, perform the steps set out below.
- In a browser, make the following request:
https://TENANT_URL_VALUE/oauth/authorize?client_id=CLIENT_ID_VALUE&redirect_uri=https://REDIRECT_URL_VALUE&code_challenge=i2q7Dklmv7xy4mznQOy1OPXVVfiQRCVFEKfHULYSCzQ&code_challenge_method=S256&scope=user_default%20offline_access&state=your_state
This will generate a code in the response URL. The code needs to be copied and used in step 2. - Using Postman (or a similar tool):
Run this POST request inserting the required values in the body of the request:
Request: https://TENANT_URL_VALUE/oauth/token
The request body needs to be modified as follows:
Insert your CODE_VALUE from step 1,
Insert your applications CLIENT_ID_VALUE, CLIENT_SECRET_VALUE, REDIRECT_URL_VALUE.
Request Body:{ "code": CODE_VALUE, "code_verifier": "1234-5678-90-ABC_DEF_GJI~jkl.mno.pqrs~tuvwxyz", "grant_type": "authorization_code", "client_id": CLIENT_ID_VALUE, "client_secret": CLIENT_SECRET_VALUE, "redirect_uri": REDIRECT_URL_VALUE }
Steps 1 and 2 need to be completed within a few seconds of each other. As the generated code from Step 1 expires after only some seconds.
If the Request made in Step 2 is successful. ADFS is configured correctly and is using Refresh Tokens.
If the request fails you will need to change your ADFS configuration.
EnvironmentThe information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
Related Content
Using Active Directory Federation Services (ADFS) as an IDP for Qlik Cloud.
ADFS single sign-on settings - In a browser, make the following request:
