Thank you for the presentation on SQLite on the UseR Meetup and for making the Jupyter notebook available today. I've been wanting to get some knowledge of SQLite. I should have asked about the difference between SQLite and PostGRESQL. . .
Glad it was helpful and thanks for attending. I've used PostgreSQL a lot in consulting. Its a great database and easy to use with R or Python. th-cam.com/video/UzD9FJ3HBd4/w-d-xo.html
Could you explain how to export DBI results to a DB file? I created a joint from two different tables of a DB, and now I want to turn this joint (DF with more than 50 million rows) into a separate DB, since I can't manipulate DBIresults with Dbplyr. That is, is it possible to outfile DBI results into a separated database?
Hi Caio, Sure. 1) Connect to real SQLite databases, i.e. files, not :memory:. 2) Then create the connection to the source database with one name like conn1 and load the dataframe from the table you desire, 3) connect to the target database as a different name like conn2, then just save the dataframe to conn2 using dbWriteTable. The video explains how to use these commands. Thanks, Bryan
Can you also consider doing a dadabrick notebook tutorial as well? I had challenges switching between python and R in notebook as such. It'd be super helpful!
Hi Bryan, I'm a BI Analyst and I'm new to r programming. I'm fairly versed in Oracle SQL and I've been learning R markdown to automate some of the fact sheets and studies we do in my office. My fact sheet has roughly 100 numbers that are being weaved into the documentation language. I have little scripts to pull each of the numbers in SQL already written. In rMarkdown I originally set up a separate code chunk to connect and store each of the numbers into a data frame then use inline code to imbed each number into the document. I quickly realized this is not the most effective way to do this. Then I started thinking to pull the population selections from the 3 DataMart tables being used into a SQLite db; then modify my select queries to pull from the popsels in the SQLite db. In your professional opinion should I continue down this path or should I bite the bullet and rewrite all of my SQL logic using dplyer? Thanks for your time and good video.
Welcome to programming! It all comes down to what you want? If this is a one off thing with lille maintenance, then it does not matter much. If it is something that will need to be modified a lot, then you want to make sure it is documented, uses clear and intuitive code, and has an architecture that supports what you are doing. Who will do the maintenance and what are their skills also needs to be considered. It sound like your source is a realtional database so SQLite may make it easier to simplify you application. For larger data volumes, PostgreSQL can also work well. It all depends on the specifics of what you are doing and what you want to accomplish.
@@BryanCafferky Im the only non-faculty employee I"m aware of, on my campus, that uses R but we have a good handful of staff that can write SQL. If you know it can be done using imbedded SQL then that's the rout I'll take. Thanks for the response and insight. I appreciate you sharing your knowledge on your videos.
Hey Bryan, i am using saqlsave() for writing data which is very very slow for 10 million rows even at a 200Mbps connection. What's the fastest way of writing from R to SQL ?
Hi Moiz, Are you writing over a connection or locally? If over a connection, I would suggest writing the data locally and then copying the SQLite database file to where you need it. it. That would remove and network speed effects. Make sense?
@@BryanCafferky writing to a remote server over a connection which is a problem. Yes i am now writing files locally in txt, (.csv cannot store more than million rows). Then I am using SQL server management studio and import/export wizard to upload the files to the database.
@@tailchopped Ok. Yes. Moving the files to the server so both are local and using Bulk Load would be the fastest. Curious though, why are you asking about SQL Server in comments to a SQLite video? See the SQL Server bcp utility for the fastest way to load flat files. docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15
Thank you for all you do. I normally don't comment.
I need to set up my weekend and watch all your videos.
Best
Glad they are helpful! I appreciate your feedback!
Thank you for the presentation on SQLite on the UseR Meetup and for making the Jupyter notebook available today. I've been wanting to get some knowledge of SQLite. I should have asked about the difference between SQLite and PostGRESQL. . .
Glad it was helpful and thanks for attending. I've used PostgreSQL a lot in consulting. Its a great database and easy to use with R or Python. th-cam.com/video/UzD9FJ3HBd4/w-d-xo.html
I added 2 more videos on PostgreSQL you might like. Intro to PostgreSQL at th-cam.com/video/UzD9FJ3HBd4/w-d-xo.html
Exactly what I am looking for,Thanks alot!
I am giving dbplyr lecture on my channel next week and this will be awesome to teach! VERY good~!! 🎉🎉🎊
Could you explain how to export DBI results to a DB file? I created a joint from two different tables of a DB, and now I want to turn this joint (DF with more than 50 million rows) into a separate DB, since I can't manipulate DBIresults with Dbplyr. That is, is it possible to outfile DBI results into a separated database?
Hi Caio,
Sure. 1) Connect to real SQLite databases, i.e. files, not :memory:. 2) Then create the connection to the source database with one name like conn1 and load the dataframe from the table you desire, 3) connect to the target database as a different name like conn2, then just save the dataframe to conn2 using dbWriteTable. The video explains how to use these commands.
Thanks,
Bryan
Can you also consider doing a dadabrick notebook tutorial as well? I had challenges switching between python and R in notebook as such. It'd be super helpful!
Yes. I did a Databricks with R video here th-cam.com/video/-vekHiJdQ1Y/w-d-xo.html
I started a Databricks series here th-cam.com/video/C496WTDhyFo/w-d-xo.html
Yeah. Lesson 0 provides the overview.
Hi Bryan, I'm a BI Analyst and I'm new to r programming. I'm fairly versed in Oracle SQL and I've been learning R markdown to automate some of the fact sheets and studies we do in my office. My fact sheet has roughly 100 numbers that are being weaved into the documentation language. I have little scripts to pull each of the numbers in SQL already written. In rMarkdown I originally set up a separate code chunk to connect and store each of the numbers into a data frame then use inline code to imbed each number into the document. I quickly realized this is not the most effective way to do this. Then I started thinking to pull the population selections from the 3 DataMart tables being used into a SQLite db; then modify my select queries to pull from the popsels in the SQLite db. In your professional opinion should I continue down this path or should I bite the bullet and rewrite all of my SQL logic using dplyer? Thanks for your time and good video.
Welcome to programming! It all comes down to what you want? If this is a one off thing with lille maintenance, then it does not matter much. If it is something that will need to be modified a lot, then you want to make sure it is documented, uses clear and intuitive code, and has an architecture that supports what you are doing. Who will do the maintenance and what are their skills also needs to be considered. It sound like your source is a realtional database so SQLite may make it easier to simplify you application. For larger data volumes, PostgreSQL can also work well. It all depends on the specifics of what you are doing and what you want to accomplish.
@@BryanCafferky Im the only non-faculty employee I"m aware of, on my campus, that uses R but we have a good handful of staff that can write SQL. If you know it can be done using imbedded SQL then that's the rout I'll take. Thanks for the response and insight. I appreciate you sharing your knowledge on your videos.
Hey Bryan, i am using saqlsave() for writing data which is very very slow for 10 million rows even at a 200Mbps connection. What's the fastest way of writing from R to SQL ?
Hi Moiz, Are you writing over a connection or locally? If over a connection, I would suggest writing the data locally and then copying the SQLite database file to where you need it. it. That would remove and network speed effects. Make sense?
@@BryanCafferky writing to a remote server over a connection which is a problem. Yes i am now writing files locally in txt, (.csv cannot store more than million rows). Then I am using SQL server management studio and import/export wizard to upload the files to the database.
@@tailchopped Ok. Yes. Moving the files to the server so both are local and using Bulk Load would be the fastest. Curious though, why are you asking about SQL Server in comments to a SQLite video?
See the SQL Server bcp utility for the fastest way to load flat files. docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15
which IDE is he using,
is it that jupyter notebook?
Yes. I used Jupyter Lab, the new and improved Jupyter Notebook.
I love this.
Great content... thanks a lot!
Glad you liked it!