Hey I am looking for help / clarification how to use or if you support “from values” syntax in SingleStore.
That would work for postgreSQL and SQL Server:
select * from (values
(1),(5),(10))s(col)
That is quite often used by the developers and quite a limitation for Single Store.
Hello Julian, Try
select * from Table(split(“Thomas,Mary,Stan”,“,”));
±-----------------------------+
| table_col |
±-----------------------------+
| Thomas |
| Mary |
| Stan |
±-----------------------------+
Hello Julian
At this point there is no direct function like lateral or Cross Apply & we are planning to have it as a general purpose feature in some point in time in future.
Can we have the exact use case you want to achieve to see other ways to achieve it in SingleStore.
Hi Julian
Below is what SingleStore Supports
SELECT * FROM TABLE([1, 5, 10]);
+-----------+
| table_col |
+-----------+
| 1 |
| 5 |
| 10 |
+-----------+
We also have few other options MPSQL array to a set of row
Hi, I just signed up for this forum and i cannot find any way to create a topic. Maybe it’s because my permissions are limited?
However, I have a question regarding loading data from parquet files into singlestore:
Is there a way to automatically map every column by name without specifying it. When using CSV you don’t need to specify any columns when loading data into singlestore. But it seems like you need to do it when loading from parquet files?
Let’s say I want to load a file into a table, but I don’t want to specify every single column name and singlestore should handle it by itself. Is this possible?
I cannot find this use case in the documentation.
Thank you
An example of loading parquet files into singlestore can be found here:
Hi ,
I am struggling to find any documentation on sqlHelper class. I am trying to run a delete query using pyspark using executeSinglestoreQuery method but i am not able to do so as i am getting the spark session has no such attributes error even though i am using the right jars and am able to read/load the data
DELIMITER //
CREATE OR REPLACE PROCEDURE audit_trail () AS
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE table_name CHAR(255);
DECLARE column_name CHAR(255);
DECLARE column_type CHAR(255);
DECLARE column_is_nullable CHAR(3);
DECLARE column_default_value TEXT;
DECLARE cur CURSOR FOR
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name NOT LIKE 'audit_trail%' AND table_name != 'schema_version';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO table_name, column_name, column_type, column_is_nullable, column_default_value;
IF done = 1 THEN
LEAVE read_loop;
END IF;
SET @audit_sql = CONCAT('INSERT INTO audit_trail(table_name, column_name, column_type, column_is_nullable, column_default_value, action) SELECT "',
table_name, '", "', column_name, '", "', column_type, '", "', column_is_nullable, '", "', column_default_value, '", "ALTERED" FROM ',
table_name, ' WHERE ', column_name, ' IS NOT NULL AND NOT EXISTS (SELECT 1 FROM audit_trail WHERE table_name = "', table_name,
'" AND column_name = "', column_name, '" AND column_default_value = "', column_default_value, '" AND action = "ALTERED")');
PREPARE stmt FROM @audit_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
Can I know whether the code works or not I have have tested but getting some error unable to find out. Help with resolution
Hi, I recently started working on SingleStore and I am new to this community. I am unable to create post in the help category. I am trying to perform INSERT WHERE NOT EXISTS in singlestore but that is not working. Can someone help me with performing the query
Query - INSERT INTO table(col1) VALUES (‘colValue’) WHERE NOT EXISTS (SELECT 1 from table WHERE col2 = ‘coValue’
Thanks in advance!
Thank you for suggestion, looks good.
Hello, I’m new to the forum. I need to ask a question or even create a new topic. I might be slow, but I can’t seem to find a button anywhere that allows me to do that. If someone from the SingleStore team can verify my access or point me in the right direction I’d appreciate it.
Thanks!
How do I start a new thread to ask a question?
Same problem here. Can’t create a new topic :-/
Hi,
How can I start a new topic?
I’ve got the new user unable to post problem too. I want to post a new thread asking about PIPELINE FS issues and how to best use. Can you give me privileges to make a new topic in Help? Thanks!
Dear Team,
I am trying to read data from singleStore via spark query. Datasize is around 500MB-1GB.
Spark version : 3.3.2
singlestore spark connector version : 4.1.4-spark-3.3.2
scala : 2.12
singlestore-jdbc-client : 1.1.8
singestore : 7.8
Dataset<Row> data = spark.read().format("singlestore").option("driver", "com.singlestore.jdbc.Driver").option("url", uat_url).option("query", query)
.option("ddlEndpoint", ddlEndpoint)
.option("user", uat_username)
.option("enableParallelRead", "automatic")
.option("parallelRead.Features", "readFromAggregatorsMaterialized,readFromAggregators")
.option("parallelRead.repartition", "true")
.option("password", uat_password).load();
int numPartitionsUsed = data. rdd().getNumPartitions();
System.out.println("Number of partitions used: " + numPartitionsUsed);
In above code, I am only getting result via single partition. data for table is spread across 20 partition. I have tried using (“numPartitions”, 2) and (“parallelRead.repartition.columns”, “id”) . Still I am getting same output. can you please help me on same ?
Hi
I just signed up as a new user on this forum yest. I came here looking for help.
I’m struggling to understand how discourse works. I’ve been unable to figure out how to create a post. kindly help
Welcome, @himawari!
Try going to SingleStore Forums
Then on the top right, click the button to create a new topic. As a new user, it may be necessary for someone to approve your first post or first few posts.
Thank you for the guidance.