My SQL connection with Rapid Miner

jdjdjdjd Member Posts: 1 Newbie
edited December 2020 in Help
Hello Hello I'm working on a project at university and there is a "hackathon" coming up ( great stuff in corona time but anyway) in wich we going to receive an .sql file and solve some sort of datamining task. thats all we know. knowing the Professor so far and how chill he is it won't be an insane task but i obviously want to be somewhat prepared. Problem... me and my group are basically noobs on this topic.
so far i somewhat decided that we use MYSQL cause as far as i know rapidminer can't load sql files in its repository. big question is now...how do i connect the mysql file to rapid miner?

i found this link (apparently im too new to post links so i try this)

community.rapidminer.com/discussion/38872/mysql-and-rapidminer

but the link leading to "as described in this Knowledge Base article." is dead 404.

so maybe someone can please help me here. 

if so...try to imagine you have to tell your grandma how she can get her .sql file into rapid miner and do some datamining stuff with it. at best with advice how to properly put the sql file into my sql in a way that it best works with rapid miner. maybe there is something i have to do there idk yet. 

i got a .sql test file from the web to play around with. it looks like this 




--
-- Database: `samplevideo_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `user_details`
--

CREATE TABLE IF NOT EXISTS `user_details` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `status` tinyint(10) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10001 ;

--
-- Dumping data for table `user_details`
--

INSERT INTO `user_details` (`user_id`, `username`, `first_name`, `last_name`, `gender`, `password`, `status`) VALUES
(1, 'rogers63', 'david', 'john', 'Female', 'e6a33eee180b07e563d74fee8c2c66b8', 1),
(2, 'mike28', 'rogers', 'paul', 'Male', '2e7dc6b8a1598f4f75c3eaa47958ee2f', 1),
(3, 'rivera92', 'david', 'john', 'Male', '1c3a8e03f448d211904161a6f5849b68', 1),
(4, 'ross95', 'maria', 'sanders', 'Male', '62f0a68a4179c5cdd997189760cbcf18', 1),
(5, 'paul85', 'morris', 'miller', 'Female', '61bd060b07bddfecccea56a82b850ecf', 1),
(6, 'smith34', 'daniel', 'michael', 'Female', '7055b3d9f5cb2829c26cd7e0e601cde5', 1),
(7, 'james84', 'sanders', 'paul', 'Female', 'b7f72d6eb92b45458020748c8d1a3573', 1),
(8, 'daniel53', 'mark', 'mike', 'Male', '299cbf7171ad1b2967408ed200b4e26c', 1),
(9, 'brooks80', 'morgan', 'maria', 'Female', 'aa736a35dc15934d67c0a999dccff8f6', 1),
(10, 'morgan65', 'paul', 'miller', 'Female', 'a28dca31f5aa5792e1cefd1dfd098569', 1),
(11, 'sanders84', 'david', 'miller', 'Female', '0629e4f9f0e01e6f20bc2066175e09f7', 1),
......and so on 



so maybe someone feels the itch to help out some noob with there problems. 

anyway...
 i wish you all the best...stay healthy in these tying times and have a good Christmas, Chanukka or what ever it is for you. 
:)


Tagged:

Answers

  • jacobcybulskijacobcybulski Member, University Professor Posts: 391 Unicorn
    edited December 2020
    You have two SQL related operators that could assist your project. One is Read Database, which has a built-in SQL query statement - if you wanted to use it you'd have to copy your SQL statements into the query text. Another is Execute SQL which allows to build the query or can take an SQL query file from disk. Both require to set up an SQL database connection, e.g. MySQL connection.
  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn
    To set up a database connection, navigate to the Connections folder in your repository, right click on it and add a connection.

    You'll need a JDBC driver for your database type and the connection information (host, port (3306 default for MySQL), database name, user name, password). You can then use this connection in the database related operators.
Sign In or Register to comment.