search

MotherDuck-Open-Source / duckdb-power-query-connector

DuckDB Power Query Custom Connector by MotherDuck
MIT License
27 stars 2 forks source link

Can't read from md #25

Closed jkleinkauff closed 3 weeks ago

jkleinkauff commented 3 weeks ago

Hey folks, thank you for this project. Would be really interesting to explore motherduck data in Power BI.

I'm trying to read data from motherduck but I can't make the connector work.

I can use the default ODBC connector, in Power BI, and query a simple .parquet file

select * from read_parquet(".parquet")

I can read a .duckdb file using this Connector, it's working fine.

When I try to read something from md, I can even list my tables on the "Navigator" page of Power BI, after the first setup. After selecting a table to load, Power BI starts the "Preview is evaluating.." and, after some time an error happens.

image

image

image

Feedback Type:
Frown (Error)

Error Message:
[Expression.Error] We cannot convert the specified value to the specified type.

Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException
Microsoft.Mashup.Engine1.Runtime.ValueException: [Expression.Error] We cannot convert the specified value to the specified type.
   at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
   at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
   at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_Value()
   at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_Value()
   at Microsoft.Mashup.Host.Document.Preview.PreviewEvaluation.<>c__DisplayClass22_0.<StartEvaluation>b__0(EvaluationResult2`1 result)

Stack Trace Message:
[Expression.Error] We cannot convert the specified value to the specified type.

Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>c__DisplayClass79_0.<OnAjaxException>b__0()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
   at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
   at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.NavigatorFloatingDialog.ShowEditLoad(IWindowHandle owner, ILegacyUIHost uiHost, Query query, ITelemetryService telemetryService, Boolean multiSelectionEnabledByDefault, ILearnedQueryImporterHost learnedQueryImporterHost, INavigatorSsoContextProvider ssoContextProvider)
   at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.ShowNavigator(Query query)
   at Microsoft.Mashup.Client.UI.Shared.DataImporter.ShowNavigatorOnSourceQuery(Query query, IEnumerable`1& importedQueries)
   at Microsoft.Mashup.Client.UI.Shared.DataImporter.ImportNavigationSource(Query query)
   at Microsoft.Mashup.Client.UI.Shared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
   at Microsoft.Mashup.Client.UI.Shared.DataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)
   at Microsoft.Mashup.Client.UI.Shared.DataImporter.<>c__DisplayClass89_0.<OnQuerySettingsResolved>b__0()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClass7_0.<RunApplicationLegacy>b__0()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.RunApplicationLegacy(String[] args)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.128.1177.0","ModuleName":"","Component":"Microsoft.Mashup.Evaluator.EvaluationHost","Error":"Microsoft.Mashup.Engine1.Runtime.ValueException","MethodDef":"OnException","ErrorOffset":"","ErrorCode":""}

Snapshot Trace Logs:
C:\Users\kleinkauff\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShotf9efcaad-8a9b-44d9-a493-b7bd71d6506a.zip

Model Default Mode:
Empty

Model Version:
PowerBI_V1

Performance Trace Logs:
C:\Users\kleinkauff\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_angularRls
PBI_backstageUI
PBI_setLabelOnExportPdf
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare
PBI_gitIntegration
PBI_newCard
PBI_buttonSlicerAuthoring
PBI_mobileAutoReportLayout
PBI_daxQueryView
PBI_daxInlineCopilot
PBI_aiNarrativesVisual
PBI_generateMeasureDescriptionsButton
PBI_desktopReportAuthoringCopilot

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_onObject
PBI_useModernPublishDialogs
PBI_tmdlInDataset
PBI_EimLessElevatedUser
PBI_modelExplorer
PBI_qnaImproveLsdlCopilot
PBI_visualCalculationsAuthoring

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBIUserFeedbackServices_IsReported:
True

Formulas:

section Section1;

shared #"NavigatorBase_08742998-058a-40f4-b58b-8787692da2f7" = let
    Source = DuckDB.Contents("md:housewatcher", "eyJhbGcid9Fls", null, [])
in
    Source;

The table I selected in PBI is pretty simple : column_name: id column_type: BIGINT

In regedit I have the path Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\DuckDB Driver configured, Driver and Setup pointing to the dlls location, version 0.9.2. - I tested version 10.0.0 but the behavior is the same.

Initially I thought the problem might be related to be running Windows ARM in a mac machine. But as I could read a parquet file through the original ODBC Connector, maybe there is something to it?

Thank you!

jkleinkauff commented 3 weeks ago

OK, found something. I think the error may be related to table with underscore in it. To reproduce the problem, create any table in Motherduck with "_" and try to select it in Power BI.

CurtHagenlocher commented 3 weeks ago

This looks like a duplicate of #12

jkleinkauff commented 3 weeks ago

It seems to be. When debugging the error that particular pr didn't got my attention. Thank you.