To connect to MySQL database, the first thing we need to do is load the extension. Run the following command in the Jupyter notebook:
%load_ext sql
Then, we have to do is initialize a connection to database:
%sql mysql://sqoopuser:NHkkP876rp@cxln2.c.thelab-240901.internal/retail_db
Basically, the format of connection URL is : mysql://username:password@host/databasename.
So by writing %sql mysql://sqoopuser:NHkkP876rp@cxln2.c.thelab-240901.internal/retail_db, we are establishing connection to the MySQL database named retail_db.
The username is sqoopuser and the password is NHkkP876rp. the host is cxln2.c.thelab-240901.internal.
Please note that the mysql username and password are different from your lab credentials which can be seen along with the Jupyter console.
Now, we can run any SQL query just like mentioned above. The only condition is that, there should be %%sql in every cell where we want to run the SQL query.
For example, to find out about tables, you can run:
%%sql
show tables
Let us create a table with your lab username. For example, if your lab username is alex2345, then we want to create a table named test_alex2345.
Create a table with your username:
%%sql
drop table if exists test_<<your lab username>>;
create table test_<<your lab username>>(name varchar(10));
Delete any existing rows:
%%sql
delete from test_<<your lab username>>;
Insert new rows:
%%sql
insert into test_<<your lab username>> values("John");
Select the data:
%%sql
select * from test_<<your lab username>>;
You just created a table, deleted the data, inserted the data into the table and selected the data from the table. Awesome!
Note - Having trouble with the assessment engine? Follow the steps listed here
Loading comments...