title: How do I convert an object of class tbl_mysql into tbl_df using dplyr?
body: <p>I am using the following code to download a mysql table and assigning it to the object <code>nycflights</code></p>
  
  <pre><code>library(dplyr)    
  my_db &lt;- src_mysql(dbname = "dplyr",
                         host = "dplyr.csrrinzqubik.us-east-1.rds.amazonaws.com",
                         port = 3306,
                         user = "dplyr",
                         password = "dplyr")
      nycflights &lt;- tbl(my_db, "dplyr")
  </code></pre>
  
  <p>Then, I would like transform this object in a dataframe of class tbl_df before doing computations using dplyr. The reason is that there are a few functions that do not work with a table of class tbl_mysql. For example</p>
  
  <pre><code>&gt; nycflights %&gt;%
   sample_n(1)
  Error: Don't know how to sample from objects of class tbl_mysql
  </code></pre>
  
  <p>The reason is that records in a mysql db don't have an intrinsic order, thus cannot be random sampled.</p>
  
  <p>Back to my questions, I tried 3 different approaches:</p>
  
  <p>First:</p>
  
  <pre><code>&gt; tbl_df(nycflights)
  Error: data is not a data frame
  </code></pre>
  
  <p>Second:</p>
  
  <pre><code>&gt; as_data_frame(nycflights)
  Error: data_frames can only contain 1d atomic vectors and lists
  </code></pre>
  
  <p>Third, which is simply too slow and it crashes after a while:</p>
  
  <pre><code>tbl_dt(nycflights)
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>The question I asked is probably of very low practical use. There are very few circumstances in which the user would need to <code>collect()</code> the whole dataset. Also, to fetch a random sample on a <code>tbl_mysql</code> object, I could use this code:</p>
  
  <pre><code>nycflights %&gt;% 
    mutate(x=rand()) %&gt;% 
    collapse() %&gt;% 
    filter(x&lt;=.0001) %&gt;% 
    select(-x) %&gt;% 
    collect()
  </code></pre>
  
  <p>Keep in mind that <code>rand()</code> is an actual mysql function. This point was confusing to me, because I was using <code>random()</code> as suggested in this <a href="https://www.rstudio.com/resources/webinars/working-with-big-data-in-r/" rel="nofollow">r studio seminar</a></p>