The user uploads feature is not exposed via SQL commands or the REST API, so to get a dataset into Dremio without visiting the UI, it would have to be part of some source (HDFS, local FS, MySQL etc) and then you would add that source to Dremio.
In the case of filesystem sources, you can also write new files to them if you’ve enabled CREATE TABLE AS (i.e. CTAS) exports on that source.
Or, you don’t want to consume your CTAS results with another tool, you can just use virtual datasets (i.e. VDS) to filter your data and then JOIN these with other sets (physical or virtual).
You should be able to submit CREATE TABLE AS or CREATE VDS statements to Dremio via your ODBC client to achieve this. When you submit the query that gives the syntax error from your R ODBC client, can you find the job in Dremio under the Jobs page?