asked on May 23, 2019
Hi, when I try to use the flexmonster to connect Elasticsearch it pops up an error message and says no living connections, Please help.
ElasticSearch version: 6.3.2
Lucene version:7.3.1
⋅ May 23, 2019
Hello,
Thank you for writing to us.
It seems that Elasticsearch may be down.
We kindly advise checking if Elasticsearch is running and that access to it is opened.
Please let us know if this works.
We are looking forward to hearing from you.
Best Regards,
Vera
⋅ May 24, 2019
Our elasticsearch is actually running, we can check the nodes status in Kibana and can bulkload index via python. Moreover, our Elasticsearch is Ali-cloud commercial service which should be stable.
Can it be a bug in flexmonster ? We are taking PoC on it to see if it fits our requirement and we may consider further procurement.
⋅ May 24, 2019
Hello, Michael,
Thank you for your reply.
We kindly advise the following:
1) If your server is up and running, in your browser go to the page where Flexmonster should be displayed and open the browser’s console to check if there are any errors in the console. These errors might point out what's going wrong.
2) This looks like a CORS-related issue. By default, the browser prevents JavaScript from making requests across domain boundaries. CORS allows web applications to make cross-domain requests. Please verify that the origin is allowed: https://www.flexmonster.com/doc/connecting-to-elasticsearch/
Please let us know if this helps.
We are looking forward to hearing from you.
Best Regards,
Vera
⋅ May 27, 2019
Hi Vera,
Thanks for your reminder. After trying 1), we found it's CORS issue and set YML, now we met another issue from the browser console - "401 (unauthorized)" (and "missing authentication token for REST request" in the UI).
We doubt if it relates to password, so we try http://<username>/<password>@<hostname>:9200, but it met ERR_NAME_NOT_RESOLVED. May I ask how to pass username/password information into flexMonster (our password includes nonalphabetic characters) ?
Regards,
Michael
⋅ May 27, 2019
Hello, Michael,
Thank you for your reply.
We would like to mention that there should be ":" instead of "/" between the username and password in the URL, for example:
https://username:password@localhost:9200
Please see https://www.elastic.co/guide/en/elasticsearch/client/javascript-api/6.x/auth-reference.html for more information.
Another way is to pass user information through an object: https://www.elastic.co/guide/en/elasticsearch/client/javascript-api/6.x/client-configuration.html
We would like to point out that instead of "node" use "host" in Flexmonster.
Please let us know if this helps to solve the issue.
We are looking forward to hearing from you.
Best Regards,
Vera
⋅ May 28, 2019
Hi Vera,
⋅ May 29, 2019
Hello, Michael,
Thank you for your reply.
Another way how username:password information can be passed is to use base64(user:password) in the authorization parameter.
For example:
username = elastic
password = justsample(_+#
The Base64 encoding for elastic:justsample(_+# will be ZWxhc3RpYzpqdXN0c2FtcGxlKF8rIw==
Then the encoded username:password can be passed with the request the following way:
Authorization: Basic ZWxhc3RpYzpqdXN0c2FtcGxlKF8rIw==
We would like to draw to your attention that the host parameter can also be an object:
"dataSource": {
"dataSourceType": "elasticsearch",
"host": {
"protocol": "https",
"host": "olap.flexmonster.com",
"port": 9200,
"headers": {
"Token": "XXX"
}
},
"index": "fm-product-sales",...
}
Here is a JSFiddle example for illustration.
The authorization parameter needs to be specified in the headers parameter of the host object.
Please let us know if this works.
We are looking forward to hearing from you.
Best Regards,
Vera
⋅ May 30, 2019
Hi Vera,
How could we apply base 64 in header ? Does it like below ?
"dataSource": {
"dataSourceType": "elasticsearch",
"host": {
"protocol": "http",
"host": "es-sg-25u1512cd0005h5uh.elasticsearch.aliyuncs.com",
"port": 9200,
"header": {
"Authorization": "Basic ZWxhc3RpYzpIRig0QjJxxxxxxx"
}
},
"index": "dummy-all-index"
}
⋅ May 30, 2019
Hello, Michael,
Thank you for writing to us.
We would like to kindly point out that the name for the parameter needs to be headers instead of header for it to work:
"host": {
"protocol": "https",
"host": "olap.flexmonster.com",
"port": 9200,
"headers": {
"Authorization": "Basic ZWxhc3RpYzpIRig0QjJxxxxxxx"
}
}
Here is a JSFiddle example for illustration.
Please let us know if this works.
We are looking forward to hearing from you.
Best Regards,
Vera
⋅ June 3, 2019
Hi Vera,
It works perfectly!
May I ask one more question on MySQL(php) connection?
I downloaded the code, and found that it used "mysql_connect($server, $username, $password)" in demo-compress-mysql.php, but our php version is 7.2, I think we should use PDO instead of mysql_connect which is no more supported after 7.0.
We tried the new code but error found in calling flexmonster-compressor, may I ask any correct sample we can refer ? Thanks !
<?php
header(‘Access-Control-Allow-Origin: *’);
header(‘Access-Control-Allow-Methods’, ‘OPTIONS,GET,PUT,POST,DELETE’);
header(‘Access-Control-Allow-Headers’, ‘Content-Type’);
header(‘Content-Type: text/plain’);
$server = “30.100.xx.xxx”;
$username = “cdw”;
$password = “xxx”;
$dbname = “cdw”;
try {
$conn = new PDO(“mysql:host=$server;dbname=$dbname”, $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully”;
$stmt = $conn->prepare(“SELECT * FROM MASTER_DATA limit 10”);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
}
catch(PDOException $e)
{
echo “Connection failed: ” . $e->getMessage();
}
//mysql_connect($server, $username, $password);
//mysql_select_db($dbname);
//$result = mysql_query(“SELECT * FROM MASTER_DATA limit 10”);
require_once(“flexmonster-compressor.php”);
Compressor::compressMySql($result);
mysql_free_result($result);
$conn->close()
?>
<br />
<b>Fatal error</b>: Uncaught Error: Call to undefined function mysql_num_fields() in C:\mk\program\xampp\htdocs\productmatrix_db\flexmonster-compressor.php:532
Stack trace:
#0 C:\mk\program\xampp\htdocs\productmatrix_db\flexmonster-compressor.php(377): MySqlReader->getNumFields(true)
#1 C:\mk\program\xampp\htdocs\productmatrix_db\flexmonster-compressor.php(367): DBReader->parseHeader(true)
#2 C:\mk\program\xampp\htdocs\productmatrix_db\flexmonster-compressor.php(18): DBReader->process(true)
#3 C:\mk\program\xampp\htdocs\productmatrix_db\demo-compress-mysql.php(33): Compressor::compressMySql(true)
#4 {main}
thrown in <b>C:\mk\program\xampp\htdocs\productmatrix_db\flexmonster-compressor.php</b> on line <b>532</b><br />
⋅ June 3, 2019
Hello, Michael,
Thank you for your reply.
We are glad to hear that the solution works fine for you.
About using PDO instead of mysql_connect, please see our connecting to a relational database with PHP guide. An example of using PDO is provided there.
Please let us know if this works.
We are looking forward to hearing from you.
Best Regards,
Vera
⋅ June 4, 2019
Hi Vera,
It worked!
But the performance is not good or say very bad, we can get the result by limiting 10 records, but keep running for more than 30 mins by limiting 1,000,000 (1m) records. Is it because we are using PHP? Any improvement to get 1m records? We tested with Tableau connecting to the same DB table which needs several seconds to generate the result.
Thanks,
Michael
⋅ June 4, 2019
Hello, Michael,
Thank you for your reply.
We are glad to hear that everything worked.
Regarding improving the performance, we kindly suggest the following recommendations:
1) One approach is to write the OCSV output to a file and load the data to Flexmonster from this file instead of directly loading the data from Flexmonster Compressor.
2) There are a few ways of improving data loading speed when using databases:
1. Use data prefiltering to load only necessary data to the client’s browser.
2. Configure GZIP compression on the server.
An important point to consider is that Elasticsearch is a better choice when working with large data sets since this way memory is used in moderation.
We would like to provide some explanations regarding the difference between Elasticsearch and other data sources (for example, CSV).
The key point is when the browser loads data:
In the case of CSV, you have to load all data before component starts displaying any report.
In the case of Elasticsearch, Flexmonster can handle much larger data because not all data is loaded into the browser at once. You have the ability to load just part of the data necessary for the specified report.
Please let us know if this helps.
We are looking forward to hearing from you.
Best Regards,
Vera
⋅ June 5, 2019
Hi Vera,
Thanks for your explanation!
And in elasticsearch data source, can we run the query for filtering extraction? e.g.
GET /dummy-all-index/_search
{
"from" : 0, "size" : 100000
}
⋅ June 5, 2019
Hello, Michael,
Thank you for your response.
We would like to mention that the subquery parameter allows setting the server-side filter which helps to decrease the size of the response from the server.
Please note that for the Elasticsearch data source type, the subquery parameter should be set as a Bool Query Object.
Let us know if everything works fine for you.
You are welcome to contact us in case any questions arise.
Best Regards,
Vera
⋅ June 6, 2019
Hi Vera,
I encounter syntax error, how to update below code?
"dataSource": {
"dataSourceType": "elasticsearch",
"host": {
"protocol": "http",
"host": "es-sg-25u1512cd0005h5uh.elasticsearch.aliyuncs.com",
"port": 9200,
"headers": {
"Authorization": "Basic ZWxhc3RpYzpIRig0Qjxxxxxx"
}
},
"index": "michael_dummy_all_tran_10_million_csv_million_rec",
"subquery": {
"from" : 0, "size" : 10
}
}
⋅ June 6, 2019
Hello, Michael,
Thank you for your question.
It seems that "from": 0, "size": 10 are not the part of bool query of Elasticsearch.
Could you please specify what you would like to achieve by using "from: ..., size: ...."?
There is a possibility that the desired output could be acquired with the help of another approach in Flexmonster.
We are looking forward to hearing from you.
Best Regards,
Vera