Databricks: Python

 

Creating like data table: Data frame and inserting data.

from pyspark.sql.types import (

    StructType,

    StructField,

    StringType,

    IntegerType,

    FloatType,

    DoubleType,

    LongType,

    TimestampType,

)

dta12 = [

    (1, "Ganesha", 30, "2021-04-02"),

    (2, "Krishna", 34, "2023-06-01"),

    (3, "Pooja", 53, "2021-01-31"),

    (4, "Archana", 56, "2021-01-28"),

]

sch1 = StructType(

    [

        StructField("id", IntegerType(), True),

        StructField("name", StringType(), True),

        StructField("age", StringType(), True),

        StructField("DateExecute", StringType(), True),

    ]

)

# print(d1);

df1= spark.createDataFrame(dta12, sch1)

display(df1)

df2=df1.filter(col("id")=="4");

#df2=df1.filter(col("id")=="4").select("name");

display(df2);

Table creation in Databricks, Add new rows 1) query 2) DataFrame

from pyspark.sql.types import StructType, StructField, StringType, IntegerType

frm1 = spark.sql("SELECT * FROM dev.databricks_training.ashwini_test")

display(frm1)

### creating data frame

sch2 = StructType(

    [

        StructField("empid1", IntegerType()),

        StructField("empname", StringType()),

        StructField("region", StringType()),

    ]

);

### creting new new to existing dataFrame

frm2=spark.createDataFrame([(123,'Speaker', 'AMIND'),(124,'Mouse', 'AMIND'),(125,'Screen', 'AMIND')], sch2)

display(frm2)

### adding new row by union

frm1=frm1.union(frm2)

display(frm1)

### saving to databricks table

frm1.write.mode("overwrite").saveAsTable(f"dev.databricks_training.ashwini_test")

### filtering columns

# display(frm1.where(frm1["region"]=="amna"));

### inerting record into table by Query execute

# spark.sql("insert into dev.databricks_training.ashwini_test values (19037,'Sri Kumar','HKT')");

Excel upload EPPlus bulk upload read excel to datatable dynamically

 

Excel upload EPPlus bulk upload read excel to datatable dynamically

public Result CrudUploadExcel()

{

    Result er = new Result();

    try

    {

        var httpRequest = HttpContext.Current.Request;

        if (httpRequest.Files.Count > 0)

        {

            var postedFile = httpRequest.Files[0];

            DataTable dtExcel = new DataTable();

            AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream, "Master");

            //FileInfo fileInfo = new FileInfo("C:\\Users\\Downloads\\ashwini20k.xlsx");

            using (ExcelPackage p = new ExcelPackage(postedFile.InputStream))

            {

                //AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader1 = new AMAT.Utilities.ExcelComponent.ExcelReader(p.Stream, "Master");

                //dtExcel = ReadExcelToTable();

                ExcelWorksheet worksheet = p.Workbook.Worksheets[1];

                int noOfCol = worksheet.Dimension.End.Column;  //get Column Count

                int noOfRow = worksheet.Dimension.End.Row;     //get row count

                int rowIndex = 1;

                for (int c = 1; c <= noOfCol; c++)

                {

                    dtExcel.Columns.Add(worksheet.Cells[rowIndex, c].Text);

                }

                rowIndex = 2;

                for (int r = rowIndex; r <= noOfRow; r++)

                {

                    DataRow dr = dtExcel.NewRow();

                    for (int c = 1; c <= noOfCol; c++)

                    {

                        dr[c - 1] = worksheet.Cells[r, c].Value;

                    }

                    dtExcel.Rows.Add(dr);

                }

            }

            if (oExcelReader.CurrentSheet == null)

            {

                er.IsSuccess = Constants.FAIL;

                er.Message = "Incorrect File.";

            }

 

                }

            }

            catch (Exception ex)

            {

                Common.WriteToLog("API", "UploadExcel()", ex);

                er.IsSuccess = Constants.FAIL;

                er.Message = ex.Message;

            }

            return er;

        }

========-=-=-=-=-= -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=-

public Result CommonCrudUploadExcel()

{

    Result er = new Result();

    try

    {

        var httpRequest = HttpContext.Current.Request;

        if (httpRequest.Files.Count > 0)

        {

            var postedFile = httpRequest.Files[0];

            DataTable dtExcel = new DataTable();

            AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream, "Master");

            using (ExcelPackage p = new ExcelPackage(postedFile.InputStream))

            {

                ExcelWorksheet worksheet = p.Workbook.Worksheets[1];

                int noOfCol = worksheet.Dimension.End.Column;

                int noOfRow = worksheet.Dimension.End.Row;

                oExcelReader.ReadDocument(noOfCol, ref dtExcel);

            }

            if (oExcelReader.CurrentSheet == null)

            {

                er.IsSuccess = Constants.FAIL;

                er.Message = "Incorrect File.";

                return er;

            }

            string user = httpRequest.Params["user"];

            string table = httpRequest.Params["table"];

 

            if (dtExcel.Rows.Count > 20000)

            {

                er.IsSuccess = Constants.FAIL;

                er.Message = "Upload limit 20,000.";

                return er;

            }

   }

            }

            catch (Exception ex)

            {

                Common.WriteToLog("API", "UploadExcel()", ex);

                er.IsSuccess = Constants.FAIL;

                er.Message = ex.Message;

            }

            return er;

        }

Python > Django

 

SQL Server connection:

srv='DRIVER={ODBC Driver 17 for SQL Server};SERVER=dca-dev-XXX;DATABASE=XXXDATABASE;uid=xxxUIDxxx;PWD=XXXXXX'

We can check ODBC driver: Start Menu => ODBC Data Source (32bit)

apexcharts implementaion, vue-apexcharts in VUE

 

apexcharts implementaion, vue-apexcharts in VUE

helper link: https://apexcharts.com/docs/vue-charts/

Step 1.

npm install --save apexcharts npm install --save vue-apexcharts

Step 2. Main.JS insert below code.

import Vue from 'vue';

import App from './App.vue';

import router from './router';

import store from './store';

import 'bootstrap'

//import 'bootstrap/dist/css/bootstrap.min.css'

import VueApexCharts from "vue-apexcharts";//*******1 apex added line in exising

//import 'jquery'

import { BootstrapVue, IconsPlugin } from 'bootstrap-vue';

import 'bootstrap/dist/css/bootstrap.css';

import 'bootstrap-vue/dist/bootstrap-vue.css';

Vue.config.productionTip = false

//Install BootstrapVue

Vue.use(BootstrapVue)

// Optionally install the BootstrapVue icon components plugin

Vue.use(IconsPlugin)

//Vue.use(bootstrap)

Vue.use(VueApexCharts);//*******2 apex added line in exising

new Vue({

  router,

  store,

  render: h => h(App)

}).$mount('#app')

Step 3.

Create a vue page Apex.vue.

<template>

  <div>

    <VueApexCharts

      width="500"

      type="bar"

      :options="options"

      :series="series"

    ></VueApexCharts>

  </div>

</template>

<script>

import VueApexCharts from "vue-apexcharts";

export default {

  name: "Apex",

  components: {

    VueApexCharts,

  },

  props: {},

  data: function () {

    return {

      srchV: "",

      Vendor: [],

      mdlPart: "",

      mdlVendor: "",

      mdlStatus: "All",

      options: {

        chart: {

          id: "vuechart-example",

          toolbar: {

            show: false,

          },

        },

        xaxis: {

          categories: [1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998],

        },

      },

      series: [

        {

          name: "series-1",

          data: [30, 40, 45, 50, 49, 60, 70, 90],

        },

      ],

    };

  },

  methods: {},

  watch: {},

  mounted: function () {},

};

</script>

<style>

</style>