Exporting Data to a MySQL-DB using R-scripting - works but indicates Error

[Ylreeb]

Hi All,

I'm using the follwing R-scripting within a function, that exports data via R-odbcDriverConnect to an MySQL database. After figureing out some issues in transforming PBI time-data types, setting the data-column in PBI to "Date/Time" and creating the corresponding DB-table with "datetime" is working so far....see Workbench screenshot:

Here is my test-setup for that scenario:

Source-Table (incl .setting the date-typ):

let
Source = #table(
{ "firstname", "lastname", "customerid", "date"},
{{"Hello","WORLD", "7", "2015-01-01"},

{"G-Day","AUSTRALIA", "8", "2015-01-02"},

{"Gruezi","SWITZERLAND", "9", "2015-02-01"}}
),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"firstname", type text}, {"lastname", type text}, {"customerid", Int64.Type}, {"date", type datetime}})
in
    #"Changed Type"

PBI-Function:

let func =  
 // Updates the db.test_pbi_r_mysql.test_pbi1.
(dataset as table) =>

let

    RScript = R.Execute(
                        "                 
                            library(RODBC)
                            conn <- odbcDriverConnect(#(0022)DSN=MTO_MySQL; database=test_pbi_r_mysql; case=nochange#(0022))
                            sqlSave(conn, dataset, tablename=#(0022)test_pbi_r_mysql.test_pbi#(0022), rownames=FALSE, append=TRUE)
                            close(conn)
                            ",
                            [dataset=dataset]),
    output = RScript{[Name="output"]}//[Value]

in

    output
, documentation = [
Documentation.Name =  " Table.ExportToMySQL
", Documentation.Description = "
" , Documentation.LongDescription = "
", Documentation.Category = " Table.Others
", Documentation.Source = " .
", Documentation.Author = " MTO
", Documentation.Examples = {[Description =  " Ask Marcus of MTO for details
 ", Result = "
"]}]
 in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

MySQL-SQL-Statement:

CREATE TABLE `test_pbi` (   
    `customerid` int(11) NOT NULL,   
    `firstname` varchar(255) DEFAULT NULL,   
    `lastname` varchar(255) DEFAULT NULL,  
    `date` datetime DEFAULT NULL,  
    PRIMARY KEY (`customerid`) )
    ENGINE=InnoDB DEFAULT CHARSET=utf8

As allready mentioned - it works in terms of appending "fresh" rows to the MySQL-table. But every (additional) function invokation indicates that a the following error in PBI(Deskop):

An error occurred in the ‘’ query. DataSource.Error: ADO.NET: R script error.
Error in sqlSave(conn, dataset, tablename = "test_pbi_r_mysql.test_pbi",  :
  unable to append to table 'test_pbi_r_mysql.test_pbi'
In addition: Warning message:
In odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  :
  character data '2015-01-01T00:00:00.0000000' truncated to 19 bytes in column 'date'
Execution halted
Warning message:
closing unused RODBC handle 1

Someone any idea??

Thank in advance,

Marcus

[Anonymous]

Hi ,

I am new to power bi. i want export data from power bi to mysql. Please can u help in understanding the things and flow with software setup.

Please help me...

Thanks,

UBG

[Ylreeb]

Hi,

it's quite easy.

You need to install and configure the R-runtime - just follow the MS instructions.

When you've processed your table in the way you wanted to store add a "Run R script" step.

In the script window fill in somekind like this:

library(RODBC)
conn <- odbcDriverConnect("DSN=ODBCDATASourceName; database=yourDBName; case=nochange")
sqlSave(conn, dataset, tablename="yourTableName.yourDBName;", rownames=FALSE, append=TRUE)
close(conn)

Ensure you've setup your MariaDB/MySQL properly so that your are able to interface it with ODBC Data Source Client on the maschine your are running PBI-Dektop on. Next you have to consider the datatypes of your culmns in PBI and your DB. Maria sometimes struggles with Date-Types exported by PBI.

As you see, the script APPENDS the PBI-table to the existing data stored in your DB. I tried to figure out just to update the data.

Therefor a (unique) index in the DB-table is in needed (also for some kind of DB maintenance). I spend nearly a day into this but run into errors in PBI (maybe the R interoperability bridge to .net)....

Regrads!!

I'm still

[Anonymous]

thank you for sharing...

i need small clarification on

library(RODBC)
conn <- odbcDriverConnect("DSN=ODBCDATASourceName; database=yourDBName; case=nochange")
sqlSave(conn, dataset, tablename="yourTableName.yourDBName;", rownames=FALSE, append=TRUE)
close(conn)

i dont know mysql, please help in understaing what are the  fields i need to update and where to see ODBCDATASourceName,yourDBName etc.

i am unble to understand syntax.

please help...

[Ylreeb]

your ODBC Data Source....highlighted the ODBCDATASourceName:

[v-yulgu-msft]

Hi @Ylreeb,

I am not familiar with R script. But, per my research, I found this article provide an example about exporting data to SQL. 

Regards,

Yuliana Gu

[Ylreeb]

Hi Yuliana Gu,

as you might have seen, my created table has some similar values as your linked article

My scenario based on that - but extended by an ID as DB primary key and a date to cover even more relevant...

But trying this based on an external data source (in this case CSV's-files) I run in this known global PBI Desktop issue "Formula.Firewall: Query references other queries, so it may not directl...."

It's a pain 😞

Regards,

Marcus