public static IList <PersonDetail> list(HqlDataReader reader)
{
IList <PersonDetail> results = null;
//Declaring the fetch size
reader.FetchSize = 12;
//Fetches the given amount of data from the resultant data
HiveResultSet result = reader.FetchResult();
//Read each row from the fetched result
foreach (HiveRecord row in result)
{
HttpContext.Current.Session["Results"] = results = result.Select(rowvalue => new PersonDetail
{
ContactId = rowvalue["contactid"].ToString(),
FullName = rowvalue["fullname"].ToString(),
Age = rowvalue["age"].ToString(),
EmailId = rowvalue["emailaddress"].ToString(),
PhoneNumber = rowvalue["phoneno"].ToString(),
ModifiedDate = rowvalue["modifieddate"].ToString()
}).ToList();
}
return(results);
}
/// <summary>
/// Thirft API to Execute the Query passed in argument.
/// </summary>
/// <param name="hostName"></param>
/// <param name="query"></param>
private static void ExecuteHqlQuery(string hostName, int port, string query)
{
//Initializing the Spark thrift server connection
HqlConnection con = new HqlConnection(hostName, port, HiveServer.HiveServer2);
con.Open();
//Creating Query to fetch all data set from the Database
HqlCommand command = new HqlCommand(query, con);
DateTime executionStartTime = DateTime.Now;
//Execution query to fetch data from Database
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = int.MaxValue;
DateTime executionEndTime = DateTime.Now;
//Fetches the result from the reader and store it in a object
HiveResultSet result = reader.FetchResult();
foreach (var row in result)
{
foreach (var data in row)
{
Console.WriteLine(data);
}
}
Console.WriteLine("Execution Start Time :" + executionStartTime);
Console.WriteLine("Execution End Time :" + executionEndTime);
con.Close();
}
public MainWindow()
{
InitializeComponent();
try
{
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Query to create AdventureWorks_Person_Contact table in Hive Database
HqlCommand createCommand = new HqlCommand("create external table IF NOT EXISTS AdventureWorks_Person_Contact(contactid int,fullname string,age int,emailaddress string,phoneno string,modifieddate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Execute query to fetch data from Hive Database
HqlDataReader reader = command.ExecuteReader();
//Assigning number of records to be fetched from Hive database
reader.FetchSize = 500;
HiveResultSet result = reader.FetchResult();
BindingList <PersonDetail> resultList = new BindingList <PersonDetail>();
//Read each row from the fetched result
foreach (HiveRecord rows in result)
{
//Adding each field to the list
resultList = new BindingList <PersonDetail>(result.Select(row => new PersonDetail
{
ContactId = Convert.ToInt32(row["contactid"]),
PersonName = row["fullname"].ToString(),
PersonAge = Convert.ToInt32(row["age"]),
EmailId = row["emailaddress"].ToString(),
PhoneNo = row["phoneno"].ToString(),
ModifiedDate = row["modifieddate"].ToString()
}).ToList());
}
//Binding the result to the grid
gridData1.ItemsSource = resultList;
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
if (MessageBox.Show("Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.", "Could not establish a connection to the HiveServer", MessageBoxButton.OK, MessageBoxImage.Warning) == MessageBoxResult.OK)
{
Application.Current.Shutdown();
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Creating the adventure person contacts table
HqlCommand createCommand = new HqlCommand("create external table IF NOT EXISTS AdventureWorks_Person_Contact(contactid int,fullname string,age int,emailaddress string,phoneno string,modifieddate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
//Creating Query to fetch all data set from Hive Database
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Execution query to fetch data from Hive Database
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = int.MaxValue;
//Fetches the result from the reader and store it in a object
HiveResultSet result = reader.FetchResult();
//Binding the fetched result to the grid
gridGroupingControl1.DataSource = result;
//Assigning Header text to Grid
gridGroupingControl1.TableDescriptor.Columns["contactid"].HeaderText = "Contact Id";
gridGroupingControl1.TableDescriptor.Columns["fullname"].HeaderText = "Full Name";
gridGroupingControl1.TableDescriptor.Columns["age"].HeaderText = "Age";
gridGroupingControl1.TableDescriptor.Columns["emailaddress"].HeaderText = "Email Address";
gridGroupingControl1.TableDescriptor.Columns["phoneno"].HeaderText = "Phone No";
gridGroupingControl1.TableDescriptor.Columns["modifieddate"].HeaderText = "Modified Date";
gridGroupingControl1.TableDescriptor.Columns["contactid"].Width = 85;
gridGroupingControl1.TableDescriptor.Columns["fullname"].Width = 170;
gridGroupingControl1.TableDescriptor.Columns["age"].Width = 90;
gridGroupingControl1.TableDescriptor.Columns["emailaddress"].Width = 230;
gridGroupingControl1.TableDescriptor.Columns["phoneno"].Width = 170;
gridGroupingControl1.TableDescriptor.Columns["modifieddate"].Width = 170;
//closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
if (MessageBox.Show("Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.", "Could not establish a connection to the HiveServer", MessageBoxButtons.OK, MessageBoxIcon.Warning) == DialogResult.OK)
{
Application.Exit();
}
}
}
public BindingList <PersonDetail> list()
{
//Initialize the connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//Open the HiveServer connection
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("create external table IF NOT EXISTS AdventureWorks_Person_Contact(contactid int,fullname string,age int,emailaddress string,phoneno string,modifieddate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("select * from AdventureWorks_Person_Contact limit 1000", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
HiveResultSet result = new HiveResultSet();
//Initialize the list to add elements in each row
BindingList <PersonDetail> resultlist = new BindingList <PersonDetail>();
while (reader.HasRows)
{
reader.FetchSize = int.MaxValue;
//Fetches the result from the reader and store it in a HiveResultSet
result = reader.FetchResult();
//Read each row from the fetched result
foreach (HiveRecord row in result)
{
resultlist = new BindingList <PersonDetail>(result.Select(rowvalue => new PersonDetail
{
ContactId = rowvalue["contactid"].ToString(),
FullName = rowvalue["fullname"].ToString(),
Age = rowvalue["age"].ToString(),
EmailId = rowvalue["emailaddress"].ToString(),
PhoneNumber = rowvalue["phoneno"].ToString(),
ModifiedDate = rowvalue["modifieddate"].ToString()
}).ToList());
}
}
//Closing the hive connection
con.Close();
return(resultlist);
}
public void Form1_Load(object sender, EventArgs e)
{
rdbExcel.Checked = true;
groupBox1.Visible = true;
groupBox2.Visible = false;
button1.Visible = true;
try
{
//Initializing the hive server connection
con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
//Open the hive server connection
con.Open();
//Creating query to create AdventureWorks_Person_Contact table in Hive Database
HqlCommand createCommand = new HqlCommand("create external table IF NOT EXISTS AdventureWorks_Person_Contact(contactid int,fullname string,age int,emailaddress string,phoneno string,modifieddate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
//Creating query to fetch data from Hive Database
command = new HqlCommand(" Select * from AdventureWorks_Person_Contact ", con);
//Execute query to fetch data from Hive Database
reader = command.ExecuteReader();
//Assigning number of records to be fetched from Hive database
reader.FetchSize = 1000;
//Fetches the result from the reader and store it in a HiveResultSet
result = reader.FetchResult();
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
if (MessageBox.Show("Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.", "Could not establish a connection to the HiveServer", MessageBoxButtons.OK, MessageBoxIcon.Warning) == DialogResult.OK)
{
Application.Exit();
}
}
}
private void BindDataSource()
{
ErrorMessage.InnerText = "";
CheckFileStatus FileStatus = new CheckFileStatus();
ErrorMessage.InnerText = FileStatus.CheckFile(path);
if (ErrorMessage.InnerText == "")
{
try
{
//Connecting to Hiveserver
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = 100;
//Fetches the result from the reader and store it in a HiveResultSet
HiveResultSet result = reader.FetchResult();
//Binding the result to the Grid
this.FlatGrid.DataSource = result;
this.FlatGrid.DataBind();
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
}
public MainWindow()
{
InitializeComponent();
groupBox1.Visibility = Visibility.Visible;
groupBox2.Visibility = Visibility.Hidden;
button1.Visibility = Visibility.Visible;
try
{
//Initializing the hive server connection
con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Creating the AdventureWorks_Person_Contact table in Hive Database
HqlCommand createCommand = new HqlCommand("create external table IF NOT EXISTS AdventureWorks_Person_Contact(contactid int,fullname string,age int,emailaddress string,phoneno string,modifieddate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
command = new HqlCommand(" Select * from AdventureWorks_Person_Contact", con);
//Query to fetch data from Hive Database
reader = command.ExecuteReader();
//Assiging number of rows to be fetched from Hive Database
reader.FetchSize = 1000;
result = reader.FetchResult();
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
if (MessageBox.Show("Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.", "Could not establish a connection to the HiveServer", MessageBoxButton.OK, MessageBoxImage.Warning) == MessageBoxResult.OK)
{
Application.Current.Shutdown();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
ErrorMessage.InnerText = "";
if (hdnGroup.Value == "Word")
{
CheckFileStatus FileStatus = new CheckFileStatus();
string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None));
ErrorMessage.InnerText = FileStatus.CheckFile(path);
if (ErrorMessage.InnerText == "")
{
try
{
//Create a new document
WordDocument document = new WordDocument();
//Adding new table to the document
WTable doctable = new WTable(document);
//Adding a new section to the document.
WSection section = document.AddSection() as WSection;
//Set Margin of the section
section.PageSetup.Margins.All = 72;
//Set page size of the section
section.PageSetup.PageSize = new SizeF(800, 792);
//Create Paragraph styles
WParagraphStyle style = document.AddParagraphStyle("Normal") as WParagraphStyle;
style.CharacterFormat.FontName = "Calibri";
style.CharacterFormat.FontSize = 11f;
//Create a character format for declaring font color and style for the text inside the cell
WCharacterFormat charFormat = new WCharacterFormat(document);
charFormat.TextColor = System.Drawing.Color.White;
charFormat.Bold = true;
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = 100;
//Fetches the result from the reader and store it in HiveResultSet
HiveResultSet result = reader.FetchResult();
//Adding headertext for the table
doctable.AddRow(true, false);
//Creating new cell
WTableCell cell = new WTableCell(document);
cell.AddParagraph().AppendText("Customer Id").ApplyCharacterFormat(charFormat);
cell.Width = 75;
//Adding cell to the row
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Full Name").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Age").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Email Id").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Phone Number").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Modified Date").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
//Reading each row from the fetched result
for (int i = 0; i < result.Count(); i++)
{
HiveRecord records = result[i];
doctable.AddRow(true, false);
//Reading each data from the row
for (int j = 0; j < records.Count; j++)
{
Object fields = records[j];
//Adding new cell to the document
cell = new WTableCell(document);
//Adding each data to the cell
cell.AddParagraph().AppendText(fields.ToString());
cell.Width = 75;
//Adding cell to the table
doctable.Rows[i + 1].Cells.Add(cell);
doctable.Rows[0].Cells[j].CellFormat.BackColor = Color.FromArgb(51, 153, 51);
}
}
//Adding table to the section
section.Tables.Add(doctable);
//Save as word 2007 format
if (rBtnWord2003.Checked == true)
{
document.Save("Sample.doc", FormatType.Doc, Response, HttpContentDisposition.Attachment);
}
else if (rBtnWord2007.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2007, Response, HttpContentDisposition.Attachment);
}
//Save as word 2010 format
else if (rbtnWord2010.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2010, Response, HttpContentDisposition.Attachment);
}
//Save as word 2013 format
else if (rbtnWord2013.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2013, Response, HttpContentDisposition.Attachment);
}
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
}
else if (hdnGroup.Value == "Excel")
{
ErrorMessage.InnerText = "";
CheckFileStatus FileStatus = new CheckFileStatus();
string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None));
ErrorMessage.InnerText = FileStatus.CheckFile(path);
if (ErrorMessage.InnerText == "")
{
try
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
//Adding header text for worksheet
worksheet[1, 1].Text = "ContactID";
worksheet[1, 2].Text = "FullName";
worksheet[1, 3].Text = "Age";
worksheet[1, 4].Text = "EmailAddress";
worksheet[1, 5].Text = "PhoneNo";
worksheet[1, 6].Text = "ModifiedDate";
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
//Passing the hive query
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = 100;
//Fetches the result from the reader and store it in a seperate set
HiveResultSet result = reader.FetchResult();
//Reading each row from the fetched result
for (int i = 0; i < result.Count(); i++)
{
HiveRecord records = result[i];
//Reading each field from the row
for (int j = 0; j < records.Count; j++)
{
Object fields = records[j];
//Assigning each field value to the worksheet based on index
worksheet[i + 2, j + 1].Text = fields.ToString();
}
}
worksheet.Range["A1:F1"].CellStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White;
worksheet.Range["A1:F1"].CellStyle.Font.Bold = true;
worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(51, 153, 51);
worksheet.UsedRange.AutofitColumns();
//Saving workbook on user relevant name
string fileName = "Sample.xlsx";
//conditions for selecting version
//Save as Excel 97to2003 format
if (rBtn2003.Checked == true)
{
workbook.Version = ExcelVersion.Excel97to2003;
workbook.SaveAs("Sample.xls", Response, ExcelDownloadType.PromptDialog);
}
//Save as Excel 2007 formt
else if (rBtn2007.Checked == true)
{
workbook.Version = ExcelVersion.Excel2007;
workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog);
}
//Save as Excel 2010 format
else if (rbtn2010.Checked == true)
{
workbook.Version = ExcelVersion.Excel2010;
workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog);
}
//Save as Excel 2013 format
else if (rbtn2013.Checked == true)
{
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog);
}
//closing the workwook
workbook.Close();
//Closing the excel engine
excelEngine.Dispose();
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
ErrorMessage.InnerText = "";
string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None));
CheckFileStatus FileStatus = new CheckFileStatus();
ErrorMessage.InnerText = FileStatus.CheckFile(path);
if (ErrorMessage.InnerText == "")
{
try
{
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = 100;
//Fetches the result from the reader and store it in a HiveResultSet set
HiveResultSet result = reader.FetchResult();
StringWriter stringWriter = new StringWriter();
//Htmlwriter for creating table to append the HiveResults
using (HtmlTextWriter writer = new HtmlTextWriter(stringWriter))
{
writer.AddAttribute(HtmlTextWriterAttribute.Border, "1");
writer.AddStyleAttribute(HtmlTextWriterStyle.BorderCollapse, "collapse");
writer.RenderBeginTag(HtmlTextWriterTag.Table);
writer.RenderBeginTag(HtmlTextWriterTag.Tr);
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write("Contact ID");
writer.RenderEndTag();
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write("Full Name");
writer.RenderEndTag();
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write("Age");
writer.RenderEndTag();
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write("Email Address");
writer.RenderEndTag();
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write("Phone No");
writer.RenderEndTag();
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write("Modified Date");
writer.RenderEndTag();
writer.RenderEndTag();
int count = result.Count();
for (int i = 0; i < count; i++)
{
writer.RenderBeginTag(HtmlTextWriterTag.Tr);
HiveRecord records = result[i];
for (int j = 0; j < records.Count; j++)
{
Object fields = records[j];
writer.RenderBeginTag(HtmlTextWriterTag.Td);
writer.Write(fields);
writer.RenderEndTag();
}
writer.RenderEndTag();
}
writer.RenderEndTag();
}
//Binding the result to the RTE control
string results = stringWriter.ToString();
rteControl.RTEContent.InnerHtml = results;
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
}
private void BindDataSource()
{
ErrorMessage.InnerText = "";
string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None));
CheckFileStatus FileStatus = new CheckFileStatus();
ErrorMessage.InnerText = FileStatus.CheckFile(path);
if (ErrorMessage.InnerText == "")
{
try
{
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact limit 100", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = int.MaxValue;
//Fetches the result from the reader and store it in a HiveResultSet
HiveResultSet result = reader.FetchResult();
//Initialize the list to add elements in each row
BindingList <PersonDetail> resultList = new BindingList <PersonDetail>();
//Read each row from the fetched result
foreach (HiveRecord rows in result)
{
//Adding element of each row to the list
resultList = new BindingList <PersonDetail>(result.Select(row => new PersonDetail
{
ContactId = Convert.ToInt32(row["contactid"]),
FullName = row["fullname"].ToString(),
Age = Convert.ToInt32(row["age"]),
EmailAddress = row["emailaddress"].ToString(),
PhoneNo = row["phoneno"].ToString(),
ModifiedDate = row["modifieddate"].ToString()
}).ToList());
}
//Binding the result to the grid
this.FlatGrid.DataSource = resultList;
this.FlatGrid.DataBind();
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Query to create AdventureWorks_Person_Contact table in Hive Database
HqlCommand createCommand = new HqlCommand("create external table IF NOT EXISTS AdventureWorks_Person_Contact(contactid int,fullname string,age int,emailaddress string,phoneno string,modifieddate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Query to fetch data from Hive Database
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = 500;
HiveResultSet resultCollection = reader.FetchResult();
BindingList <PersonDetail> result = new BindingList <PersonDetail>();
//Reading each row from the fetched result
foreach (HiveRecord rows in resultCollection)
{
//Adding each field value to the list
result = new BindingList <PersonDetail>(resultCollection.Select(row => new PersonDetail
{
ContactId = Convert.ToInt32(row["contactid"]),
FullName = row["fullname"].ToString(),
Age = Convert.ToInt32(row["age"]),
EmailAddress = row["emailaddress"].ToString(),
PhoneNo = row["phoneno"].ToString(),
ModifiedDate = row["modifieddate"].ToString()
}).ToList());
}
//Binding the result to the grid
gridGroupingControl1.DataSource = result;
gridGroupingControl1.TableDescriptor.Columns["ContactId"].HeaderText = "Contact Id";
gridGroupingControl1.TableDescriptor.Columns["FullName"].HeaderText = "Full Name";
gridGroupingControl1.TableDescriptor.Columns["Age"].HeaderText = "Age";
gridGroupingControl1.TableDescriptor.Columns["EmailAddress"].HeaderText = "Email Address";
gridGroupingControl1.TableDescriptor.Columns["PhoneNo"].HeaderText = "Phone No";
gridGroupingControl1.TableDescriptor.Columns["ModifiedDate"].HeaderText = "Modified Date";
gridGroupingControl1.TableDescriptor.Columns["ContactId"].Width = 85;
gridGroupingControl1.TableDescriptor.Columns["FullName"].Width = 170;
gridGroupingControl1.TableDescriptor.Columns["Age"].Width = 90;
gridGroupingControl1.TableDescriptor.Columns["EmailAddress"].Width = 230;
gridGroupingControl1.TableDescriptor.Columns["PhoneNo"].Width = 170;
gridGroupingControl1.TableDescriptor.Columns["ModifiedDate"].Width = 170;
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
if (MessageBox.Show("Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.", "Could not establish a connection to the HiveServer", MessageBoxButtons.OK, MessageBoxIcon.Warning) == DialogResult.OK)
{
Application.Exit();
}
}
}